set tidb_partition_prune_mode dynamic,IndexJoin return incorrect record

[vivid392845427]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set @@session.tidb_partition_prune_mode = 'dynamic';
drop table t1,t2;
create table t1 (c_int int, c_str varchar(40), primary key (c_int, c_str) clustered, key(c_int) ) partition by hash (c_int) partitions 4 ;
create table t2 like t1 ;
insert into t1 values(6, 'beautiful curran');
insert into t1 values(7, 'epic kalam');
insert into t1 values(7, 'affectionate curie');
insert into t2 values(6, 'vigorous rhodes');
insert into t2 values(7, 'sweet aryabhata' );

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

mysql> select * from t1, t2 where t1.c_int = t2.c_int and t1.c_str <= t2.c_str and t2.c_int in (6, 7, 6);
+-------+--------------------+-------+-----------------+
| c_int | c_str | c_int | c_str |
+-------+--------------------+-------+-----------------+
| 6 | beautiful curran | 6 | vigorous rhodes |
| 7 | affectionate curie | 7 | sweet aryabhata |
| 7 | epic kalam | 7 | sweet aryabhata |
+-------+--------------------+-------+-----------------+
3 rows in set (0.01 sec)

3. What did you see instead (Required)

mysql> select * from t1, t2 where t1.c_int = t2.c_int and t1.c_str <= t2.c_str and t2.c_int in (6, 7, 6);
+-------+--------------------+-------+-----------------+
| c_int | c_str | c_int | c_str |
+-------+--------------------+-------+-----------------+
| 6 | beautiful curran | 6 | vigorous rhodes |
| 7 | affectionate curie | 7 | sweet aryabhata |
| 7 | affectionate curie | 7 | sweet aryabhata |
| 7 | epic kalam | 7 | sweet aryabhata |
| 7 | epic kalam | 7 | sweet aryabhata |
+-------+--------------------+-------+-----------------+
5 rows in set (0.00 sec)

mysql> explain select * from t1, t2 where t1.c_int = t2.c_int and t1.c_str <= t2.c_str and t2.c_int in (6, 7, 6);
+-----------------------------+---------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+---------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_13 | 25.00 | root | | inner join, inner:TableReader_12, outer key:test1.t1.c_int, inner key:test1.t2.c_int, equal cond:eq(test1.t1.c_int, test1.t2.c_int), other cond:le(test1.t1.c_str, test1.t2.c_str) |
| ├─TableReader_27(Build) | 20.00 | root | partition:p2,p3 | data:TableRangeScan_26 |
| │ └─TableRangeScan_26 | 20.00 | cop[tikv] | table:t1 | range:[6,6], [7,7], keep order:false, stats:pseudo |
| └─TableReader_12(Probe) | 0.00 | root | partition:p2,p3 | data:Selection_11 |
| └─Selection_11 | 0.00 | cop[tikv] | | in(test1.t2.c_int, 6, 7, 6) |
| └─TableRangeScan_10 | 1.00 | cop[tikv] | table:t2 | range: decided by [test1.t1.c_int], keep order:false, stats:pseudo |
+-----------------------------+---------+-----------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set, 2 warnings (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v6.0.0-alpha-115-g80ca34256
Edition: Community
Git Commit Hash: 80ca342
Git Branch: master
UTC Build Time: 2022-03-16 10:26:51
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[bb7133]

Some updates:

There is something wrong with the IndexJoin with predicate t1.c_str <= t2.c_str, the following SQL gives correct result:

explain select * from t1, t2 where t1.c_int = t2.c_int and t2.c_int in (6, 7, 6);

tidb> explain select * from t1, t2 where t1.c_int = t2.c_int and t2.c_int in (6, 7, 6);
+-----------------------------+---------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------------------------+
| id                          | estRows | task      | access object   | operator info                                                                                                                   |
+-----------------------------+---------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_13                | 25.00   | root      |                 | inner join, inner:TableReader_12, outer key:test.t1.c_int, inner key:test.t2.c_int, equal cond:eq(test.t1.c_int, test.t2.c_int) |
| ├─TableReader_27(Build)     | 20.00   | root      | partition:p2,p3 | data:TableRangeScan_26                                                                                                          |
| │ └─TableRangeScan_26       | 20.00   | cop[tikv] | table:t1        | range:[6,6], [7,7], keep order:false, stats:pseudo                                                                              |
| └─TableReader_12(Probe)     | 0.00    | root      | partition:p2,p3 | data:Selection_11                                                                                                               |
|   └─Selection_11            | 0.00    | cop[tikv] |                 | in(test.t2.c_int, 6, 7, 6)                                                                                                      |
|     └─TableRangeScan_10     | 1.00    | cop[tikv] | table:t2        | range: decided by [test.t1.c_int], keep order:false, stats:pseudo                                                               |
+-----------------------------+---------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

tidb> select * from t1, t2 where t1.c_int = t2.c_int and t2.c_int in (6, 7, 6);
+-------+--------------------+-------+-----------------+
| c_int | c_str              | c_int | c_str           |
+-------+--------------------+-------+-----------------+
|     7 | affectionate curie |     7 | sweet aryabhata |
|     7 | epic kalam         |     7 | sweet aryabhata |
|     6 | beautiful curran   |     6 | vigorous rhodes |
+-------+--------------------+-------+-----------------+

When digging deeper, there is something wrong with the lookUpContent:

With t1.c_str <= t2.c_str, it contains 3 keys: [6, 7, 7] and the result is incorrect.
Without t1.c_str <= t2.c_str, it contains 2 keys: [6, 7] and the result is correct.

Another update:

The result of lookUpContent is correct(3 elements with t1.c_str <= t2.c_str), but it failed merging into 2 kv ranges in buildTableReaderForIndexJoin, which should be fixed.