Randgen test tiflash result not correct

[lilinghai]

5.2 version
expect the result is 89 , but got 100.
download and source the file
1.txt

select count(*) from table_100_utf8mb4collate_utf8mb4_general_ci_undef as t1 where t1. `col_varchar(20)_key_signed` not in (select `col_char(20)_key_signed` from table_10_utf8mb4collate_utf8mb4_general_ci_undef as t2 where t1. `col_varchar(20)_key_signed` not in (  `col_varchar(20)_undef_signed`, t1. `col_varbinary(20)_key_signed`,  `col_char(20)_key_signed`, t1. `col_varbinary(20)_undef_signed` ));

plan is

+------------------------------------------+---------+-------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                       | estRows | task              | access object | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+------------------------------------------+---------+-------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_31                               | 1.00    | root              |               | funcs:count(Column#28)->Column#27                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| └─TableReader_33                         | 1.00    | root              |               | data:ExchangeSender_32                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|   └─ExchangeSender_32                    | 1.00    | batchCop[tiflash] |               | ExchangeType: PassThrough                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|     └─HashAgg_12                         | 1.00    | batchCop[tiflash] |               | funcs:count(1)->Column#28                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|       └─HashJoin_30                      | 80.00   | batchCop[tiflash] |               | CARTESIAN anti semi join, other cond:eq(collation2.table_100_utf8mb4collate_utf8mb4_general_ci_undef.col_varchar(20)_key_signed, collation2.table_10_utf8mb4collate_utf8mb4_general_ci_undef.col_char(20)_key_signed), not(in(collation2.table_100_utf8mb4collate_utf8mb4_general_ci_undef.col_varchar(20)_key_signed, collation2.table_10_utf8mb4collate_utf8mb4_general_ci_undef.col_varchar(20)_undef_signed, collation2.table_100_utf8mb4collate_utf8mb4_general_ci_undef.col_varbinary(20)_key_signed, collation2.table_10_utf8mb4collate_utf8mb4_general_ci_undef.col_char(20)_key_signed, collation2.table_100_utf8mb4collate_utf8mb4_general_ci_undef.col_varbinary(20)_undef_signed)) |
|         ├─ExchangeReceiver_19(Build)     | 10.00   | batchCop[tiflash] |               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
|         │ └─ExchangeSender_18            | 10.00   | batchCop[tiflash] |               | ExchangeType: Broadcast                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
|         │   └─TableFullScan_17           | 10.00   | batchCop[tiflash] | table:t2      | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|         └─TableFullScan_16(Probe)        | 100.00  | batchCop[tiflash] | table:t1      | keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+------------------------------------------+---------+-------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[LittleFall]

0823 nightly, no collation. result correct.

mysql> select count(*) from table_100_utf8mb4collate_utf8mb4_general_ci_undef as t1 where t1. `col_varchar(20)_key_signed` not in (select `col_char(20)_key_signed` from table_10_utf8mb4collate_utf8mb4_general_ci_undef as t2 where t1. `col_varchar(20)_key_signed` not in (  `col_varchar(20)_undef_signed`, t1. `col_varbinary(20)_key_signed`,  `col_char(20)_key_signed`, t1. `col_varbinary(20)_undef_signed` ));
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

mysql> set tidb_isolation_read_engines=tiflash;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from table_100_utf8mb4collate_utf8mb4_general_ci_undef as t1 where t1. `col_varchar(20)_key_signed` not in (select `col_char(20)_key_signed` from table_10_utf8mb4collate_utf8mb4_general_ci_undef as t2 where t1. `col_varchar(20)_key_signed` not in (  `col_varchar(20)_undef_signed`, t1. `col_varbinary(20)_key_signed`,  `col_char(20)_key_signed`, t1. `col_varbinary(20)_undef_signed` ));
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.02 sec)

| tidb | 127.0.0.1:4000  | new_collations_enabled_on_first_bootstrap                   | false

[LittleFall]

with collation open,

tikv: 89

tiflash: 100

[LittleFall]

minimal reproduce:

create table q1(a varchar(20), b varbinary(20)) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
alter table q1 set tiflash replica 1;
insert into q1 values ('a', 'A');

create table q2(a char(20)) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
alter table q2 set tiflash replica 1;
insert into q2 values ('A');

set SESSION tidb_isolation_read_engines = tikv;
select * from q1
where q1.a not in (
    select a from q2
    where q1.a not in (q1.b, q2.a)
);
-- no data

set SESSION tidb_isolation_read_engines = tiflash;
select * from q1
where q1.a not in (
    select a from q2
    where q1.a not in (q1.b, q2.a)
);
-- a,A

[LittleFall]

-- consistent: both "a, A"
select * from q1
where q1.a not in (
select a from q2
where q1.a <> q1.b and q1.a <> q2.a
);

-- inconsistent, tiflash "a,A", tikv empty.
select * from q1
where q1.a not in (
select a from q2
where q1.a not in (q1.b, q2.a)
);

[fuzhe1989]

-- 一致，a, A
select * from q1
where q1.a not in (
select a from q2
where q1.a <> q1.b and q1.a <> q2.a
);

-- 不一致, tiflash 为 a,A，tikv 为空
select * from q1
where q1.a not in (
select a from q2
where q1.a not in (q1.b, q2.a)
);

Chinese lihai

[LittleFall]

-- 一致，a, A
select * from q1
where q1.a not in (
select a from q2
where q1.a <> q1.b and q1.a <> q2.a
);
-- 不一致, tiflash 为 a,A，tikv 为空
select * from q1
where q1.a not in (
select a from q2
where q1.a not in (q1.b, q2.a)
);

Chinese lihai

sorry, fixed.