enable-async-apply-prewrite = true, query result is incorrect with index merge

[seiya-annie]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

cluster-a:
tidb:
config: |
max-index-length = 12287
new_collations_enabled_on_first_bootstrap = true
[experimental]
allow-expression-index = true
tikv:
config: |
[storage]
reserve-space = 0
enable-async-apply-prewrite = true
[pessimistic-txn]
pipelined = true
in-memory = true

cluster-b:
tikv:
config: |
[storage]
reserve-space = "0MB"
tidb:
config: |
max-index-length = 12287
new_collations_enabled_on_first_bootstrap = true
[experimental]
allow-expression-index = true

the default value of "enable-async-apply-prewrite" is false.

create table tbl_43 ( col_304 binary(207) collate binary default 'eIenHx' not null , primary key idx_258 ( col_304 ) , unique key idx_259 ( col_304(5) ) , unique key idx_260 ( col_304(2) ) , key idx_261 ( col_304 ) , unique key idx_262 ( col_304 ) ) collate utf8_bin;
 replace into tbl_43  values ( 'BCmuENPHzSOIMJLPB' ) , ( 'TloTqcHhdgpwvMsSoJ' ) , ( 'LDOdXZYpOR') , ( 'UajN' ) , ( 'mAwLZbiyq' ) , ( 'R' ) , ( 'swLIoWa' );
 select max( col_304 ) from (select  /*+ use_index_merge( tbl_43 ) */ * from tbl_43 where not( tbl_43.col_304 between 'YEpfYfPVvhMlHGHSMKm' and 'PE' ) or tbl_43.col_304 in ( 'LUBGzGMA' ) and tbl_43.col_304 between 'HpsjfuSReCwBoh' and 'fta' or not( tbl_43.col_304 between 'MFWmuOsoyDv' and 'TSeMYpDXnFIyp' ) order by col_304  )  ordered_tbl  ;

2. What did you expect to see? (Required)

the result should be same in two clusters

3. What did you see instead (Required)

in cluster-a:

mysql> select max( col_304 ) from (select  /*+ use_index_merge( tbl_43 ) */ * from tbl_43 where not( tbl_43.col_304 between 'YEpfYfPVvhMlHGHSMKm' and 'PE' ) or tbl_43.col_304 in ( 'LUBGzGMA' ) and tbl_43.col_304 between 'HpsjfuSReCwBoh' and 'fta' or not( tbl_43.col_304 between 'MFWmuOsoyDv' and 'TSeMYpDXnFIyp' ) order by col_304  )  ordered_tbl  ;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| max( col_304 )                                                                                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0x42436D75454E50487A534F494D4A4C504200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

in cluster-b:

mysql> select max( col_304 ) from (select  /*+ use_index_merge( tbl_43 ) */ * from tbl_43 where not( tbl_43.col_304 between 'YEpfYfPVvhMlHGHSMKm' and 'PE' ) or tbl_43.col_304 in ( 'LUBGzGMA' ) and tbl_43.col_304 between 'HpsjfuSReCwBoh' and 'fta' or not( tbl_43.col_304 between 'MFWmuOsoyDv' and 'TSeMYpDXnFIyp' ) order by col_304  )  ordered_tbl  ;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| max( col_304 )                                                                                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0x73774C496F57610000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

results are same for below sqls:
select /*+ use_index_merge( tbl_43 ) */ * from tbl_43 where not( tbl_43.col_304 between 'YEpfYfPVvhMlHGHSMKm' and 'PE' ) or tbl_43.col_304 in ( 'LUBGzGMA' ) and tbl_43.col_304 between 'HpsjfuSReCwBoh' and 'fta' or not( tbl_43.col_304 between 'MFWmuOsoyDv' and 'TSeMYpDXnFIyp' ) order by col_304 ;
select max( col_304 ) from tbl_43;
select * from tbl_43;

4. What is your TiDB version? (Required)

tidb: e8c397f
tikv: af169fc72dada426c9952200db1d6322ab7990e9

[seiya-annie]

cluster-a:

cluster-b:

[seiya-annie]

[AilinKid]

#52947