new collation + merge join get wrong result

[time-and-fate]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

new collation: on (confirmed that this is needed to trigger this bug)

clustered_index_go_c.tbl_2-schema.sql.txt
clustered_index_go_c.tbl_2.000000000.sql.txt

select /*+  MERGE_JOIN( tbl_2 , tbl_2 ) */ tbl_2.* from tbl_2 where col_13 in ( select col_10 from tbl_2 where tbl_2.col_13 in ( 'Vftbh' , 'YjwPNaVCAJtKs' , 'DXMcdpfWCs' , 'peiJPqRH' , 'KInBGfejejv' , 'OFZi' , 'MBlDV' ) ) order by col_10 , col_11 , col_12 , col_13 , col_14 limit 901 ;

Sometimes the bug might not appear because the query plan is unstable.

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

> select tbl_2.* from tbl_2 where col_13 in ( select col_10 from tbl_2 where tbl_2.col_13 in ( 'Vftbh' , 'YjwPNaVCAJtKs' , 'DXMcdpfWCs' , 'peiJPqRH' , 'KInBGfejejv' , 'OFZi' , 'MBlDV' ) ) order by col_10 , col_11 , col_12 , col_13 , col_14 limit 901 ;
+---------------+----------+--------------+--------+--------+
| col_10        | col_11   | col_12       | col_13 | col_14 |
+---------------+----------+--------------+--------+--------+
| h             |  3.04000 | HnyTNtGOGQcq | E      |    239 |
| zVImrWKTcuSlk | 43.37300 | LTwNDBR      | E      |     88 |
+---------------+----------+--------------+--------+--------+
2 rows in set (0.034 sec)

3. What did you see instead (Required)

> select /*+  MERGE_JOIN( tbl_2 , tbl_2 ) */ tbl_2.* from tbl_2 where col_13 in ( select col_10 from tbl_2 where tbl_2.col_13 in ( 'Vftbh' , 'YjwPNaVCAJtKs' , 'DXMcdpfWCs' , 'peiJPqRH' , 'KInBGfejejv' , 'OFZi' , 'MBlDV' ) ) order by col_10 , col_11 , col_12 , col_13 , col_14 limit 901 ;
Empty set, 1 warning (0.034 sec)

4. What is your TiDB version? (Required)

master

[time-and-fate]

query plan with the correct result (removed some irrelevant fields for clarity)

+---------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-
| id                              | estRows | actRows | task      | access object | operator info                                                                                                                                                                                                       | 
+---------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-
| TopN_14                         | 790.40  | 2       | root      |               | test.tbl_2.col_10, test.tbl_2.col_11, test.tbl_2.col_12, test.tbl_2.col_13, test.tbl_2.col_14, offset:0, count:901                                                                                                  | 
| └─HashJoin_34                   | 790.40  | 2       | root      |               | semi join, equal:[eq(test.tbl_2.col_13, test.tbl_2.col_10)]                                                                                                                                                         | 
|   ├─TableReader_39(Build)       | 6.91    | 5       | root      |               | data:Selection_38                                                                                                                                                                                                   | 
|   │ └─Selection_38              | 6.91    | 5       | cop[tikv] |               | not(isnull(test.tbl_2.col_10))                                                                                                                                                                                      | 
|   │   └─TableRangeScan_37       | 6.92    | 5       | cop[tikv] | table:tbl_2   | range:["DXMcdpfWCs","DXMcdpfWCs"], ["KInBGfejejv","KInBGfejejv"], ["MBlDV","MBlDV"], ["OFZi","OFZi"], ["peiJPqRH","peiJPqRH"], ["Vftbh","Vftbh"], ["YjwPNaVCAJtKs","YjwPNaVCAJtKs"], keep order:false, stats:pseudo | 
|   └─TableReader_36(Probe)       | 988.00  | 988     | root      |               | data:TableFullScan_35                                                                                                                                                                                               | 
|     └─TableFullScan_35          | 988.00  | 988     | cop[tikv] | table:tbl_2   | keep order:false, stats:pseudo                                                                                                                                                                                      | 
+---------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-

query plan with the wrong result

+-------------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                  | estRows | actRows | task      | access object | operator info                                                                                                                                                                                                       |
+-------------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Limit_17                            | 790.40  | 0       | root      |               | offset:0, count:901                                                                                                                                                                                                 |
| └─Sort_63                           | 790.40  | 0       | root      |               | test.tbl_2.col_10, test.tbl_2.col_11, test.tbl_2.col_12, test.tbl_2.col_13, test.tbl_2.col_14                                                                                                                       |
|   └─MergeJoin_48                    | 790.40  | 0       | root      |               | semi join, left key:test.tbl_2.col_13, right key:test.tbl_2.col_10                                                                                                                                                  |
|     ├─Sort_62(Build)                | 6.91    | 5       | root      |               | test.tbl_2.col_10                                                                                                                                                                                                   |
|     │ └─TableReader_55              | 6.91    | 5       | root      |               | data:Selection_54                                                                                                                                                                                                   |
|     │   └─Selection_54              | 6.91    | 5       | cop[tikv] |               | not(isnull(test.tbl_2.col_10))                                                                                                                                                                                      |
|     │     └─TableRangeScan_53       | 6.92    | 5       | cop[tikv] | table:tbl_2   | range:["DXMcdpfWCs","DXMcdpfWCs"], ["KInBGfejejv","KInBGfejejv"], ["MBlDV","MBlDV"], ["OFZi","OFZi"], ["peiJPqRH","peiJPqRH"], ["Vftbh","Vftbh"], ["YjwPNaVCAJtKs","YjwPNaVCAJtKs"], keep order:false, stats:pseudo |
|     └─TableReader_52(Probe)         | 988.00  | 988     | root      |               | data:TableFullScan_51                                                                                                                                                                                               |
|       └─TableFullScan_51            | 988.00  | 988     | cop[tikv] | table:tbl_2   | keep order:true, stats:pseudo                                                                                                                                                                                       |
+-------------------------------------+---------+---------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Seems the bug is related to the merge join.

[zz-jason]

you can add hint to the query to make the reproduce step stable.

[zz-jason]

how did you find this bug?

[time-and-fate]

you can add hint to the query to make the reproduce step stable.

Yes. The hint should make it more stable to reproduce, but it's not 100% when I'm trying.

In fact, the hint is necessary to reproduce it most times, but tidb reported a warning against this hint, which should mean this hint is ineffective. There may be some other reasons.

how did you find this bug?

It occurred when I'm testing clustered index using random test.

[XuHuaiyu]

tidb>  select /*+ merge_join(tbl_2, tbl_2)*/ tbl_2.* from tbl_2 use index(primary) where col_13 in ( select col_10 from tbl_2 where tbl_2.col_13 in ( 'Vftbh' , 'YjwPNaVCAJtKs' , 'DXMcdpfWCs' , 'peiJPqRH' , 'KInBGfejejv' , 'OFZi' , 'MBlDV' ) ) order by col_10 , col_11 , col_12 , col_13 , col_14 limit 901 ;
Empty set, 1 warning (0.00 sec)

-- without order by
tidb> select /*+ merge_join(tbl_2, tbl_2)*/ tbl_2.* from tbl_2 use index(primary) where col_13 in ( select col_10 from tbl_2 where tbl_2.col_13 in ( 'DXMcdpfWCs' , 'peiJPqRH' , 'KInBGfejejv' , 'OFZi' , 'MBlDV' ) )  ;
+---------------+----------+--------------+--------+--------+
| col_10        | col_11   | col_12       | col_13 | col_14 |
+---------------+----------+--------------+--------+--------+
| h             |  3.04000 | HnyTNtGOGQcq | E      |    239 |
| zVImrWKTcuSlk | 43.37300 | LTwNDBR      | E      |     88 |
+---------------+----------+--------------+--------+--------+

[XuHuaiyu]

tidb> desc select /*+ merge_join(tbl_2, tbl_2)*/ tbl_2.* from tbl_2 use index(primary) where col_13 in ( select col_10 from tbl_2 where tbl_2.col_13 in ( 'Vftbh' , 'YjwPNaVCAJtKs' , 'DXMcdpfWCs' , 'peiJPqRH' , 'KInBGfejejv' , 'OFZi' , 'MBlDV' ) ) order by col_10 , col_11 , col_12 , col_13 , col_14 limit 901 ;
+------------------------------------------+---------+-----------+----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| id                                       | estRows | task      | access object                                      | operator info                                                                                                                             |
+------------------------------------------+---------+-----------+----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Limit_17                                 | 790.40  | root      |                                                    | offset:0, count:901                                                                                                                       |
| └─Sort_76                                | 790.40  | root      |                                                    | test.tbl_2.col_10, test.tbl_2.col_11, test.tbl_2.col_12, test.tbl_2.col_13, test.tbl_2.col_14                                             |
|   └─MergeJoin_51                         | 790.40  | root      |                                                    | semi join, left key:test.tbl_2.col_13, right key:test.tbl_2.col_10                                                                        |
|     ├─Sort_75(Build)                     | 6.91    | root      |                                                    | test.tbl_2.col_10                                                                                                                         |
|     │ └─IndexLookUp_74                   | 6.91    | root      |                                                    |                                                                                                                                           |
|     │   ├─IndexRangeScan_71(Build)       | 6.92    | cop[tikv] | table:tbl_2, index:idx_5(col_13, col_14)           | range:["DX","DX"], ["KI","KI"], ["MB","MB"], ["OF","OF"], ["pe","pe"], ["Vf","Vf"], ["Yj","Yj"], keep order:false, stats:pseudo           |
|     │   └─Selection_73(Probe)            | 6.91    | cop[tikv] |                                                    | in(test.tbl_2.col_13, "Vftbh", "YjwPNaVCAJtKs", "DXMcdpfWCs", "peiJPqRH", "KInBGfejejv", "OFZi", "MBlDV"), not(isnull(test.tbl_2.col_10)) |
|     │     └─TableRowIDScan_72            | 6.92    | cop[tikv] | table:tbl_2                                        | keep order:false, stats:pseudo                                                                                                            |
|     └─Projection_59(Probe)               | 988.00  | root      |                                                    | test.tbl_2.col_10, test.tbl_2.col_11, test.tbl_2.col_12, test.tbl_2.col_13, test.tbl_2.col_14                                             |
|       └─IndexLookUp_58                   | 988.00  | root      |                                                    |                                                                                                                                           |
|         ├─IndexFullScan_56(Build)        | 988.00  | cop[tikv] | table:tbl_2, index:PRIMARY(col_13, col_11, col_14) | keep order:true, stats:pseudo                                                                                                             |
|         └─TableRowIDScan_57(Probe)       | 988.00  | cop[tikv] | table:tbl_2                                        | keep order:false, stats:pseudo                                                                                                            |
+------------------------------------------+---------+-----------+----------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set, 1 warning (0.00 sec)

-- without order by 
tidb> desc select /*+ merge_join(tbl_2, tbl_2)*/ tbl_2.* from tbl_2 use index(primary) where col_13 in ( select col_10 from tbl_2 where tbl_2.col_13 in ( 'DXMcdpfWCs' , 'peiJPqRH' , 'KInBGfejejv' , 'OFZi' , 'MBlDV' ) )  ;
+------------------------------------+---------+-----------+----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+
| id                                 | estRows | task      | access object                                      | operator info                                                                                                   |
+------------------------------------+---------+-----------+----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+
| MergeJoin_9                        | 790.40  | root      |                                                    | semi join, other cond:eq(test.tbl_2.col_13, test.tbl_2.col_10)                                                  |
| ├─IndexLookUp_27(Build)            | 4.94    | root      |                                                    |                                                                                                                 |
| │ ├─IndexRangeScan_24(Build)       | 4.94    | cop[tikv] | table:tbl_2, index:idx_5(col_13, col_14)           | range:["DX","DX"], ["KI","KI"], ["MB","MB"], ["OF","OF"], ["pe","pe"], keep order:false, stats:pseudo           |
| │ └─Selection_26(Probe)            | 4.94    | cop[tikv] |                                                    | in(test.tbl_2.col_13, "DXMcdpfWCs", "peiJPqRH", "KInBGfejejv", "OFZi", "MBlDV"), not(isnull(test.tbl_2.col_10)) |
| │   └─TableRowIDScan_25            | 4.94    | cop[tikv] | table:tbl_2                                        | keep order:false, stats:pseudo                                                                                  |
| └─IndexLookUp_12(Probe)            | 988.00  | root      |                                                    |                                                                                                                 |
|   ├─IndexFullScan_10(Build)        | 988.00  | cop[tikv] | table:tbl_2, index:PRIMARY(col_13, col_11, col_14) | keep order:false, stats:pseudo                                                                                  |
|   └─TableRowIDScan_11(Probe)       | 988.00  | cop[tikv] | table:tbl_2                                        | keep order:false, stats:pseudo                                                                                  |
+------------------------------------+---------+-----------+----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions