executor: IndexJoin get wrong results when inner range is decided by more than one condition

[francis0407]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

mysql> create table t1(a int not null, b int not null, c int, d int, key idx_a_b(a,b));
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(a int not null, b int not null, c int, d int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (0,1,0,1),(0,2,0,1),(0,3,0,1),(0,4,0,1),(1,1,0,1),(1,2,0,1);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (0,1,0,0),(0,2,0,1),(0,3,0,2),(0,4,0,3),(1,5,0,4),(1,6,0,5),(1,7,0,6),(1,8,0,7),(1,9,0,8);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select /*+ TIDB_INLJ(t1) */ count(*) from t2 left join t1 on t1.a = t2.a and t1.b < t2.b;
+----------+
| count(*) |
+----------+
|       65 |
+----------+
1 row in set (0.00 sec)

mysql> select /*+ TIDB_HJ(t1) */ count(*) from t2 left join t1 on t1.a = t2.a and t1.b < t2.b;
+----------+
| count(*) |
+----------+
|       17 |
+----------+
1 row in set (0.01 sec)

mysql> desc select /*+ TIDB_INLJ(t1) */ count(*) from t2 left join t1 on t1.a = t2.a and t1.b < t2.b;
+--------------------------+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| id                       | count | task | operator info                                                                                                               |
+--------------------------+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_11             | 1.00  | root | funcs:count(1)                                                                                                              |
| └─IndexJoin_19           | 9.00  | root | left outer join, inner:IndexReader_18, outer key:test.t2.a, inner key:test.t1.a, other cond:lt(test.t1.b, test.t2.b)        |
|   ├─TableReader_16       | 9.00  | root | data:TableScan_15                                                                                                           |
|   │ └─TableScan_15       | 9.00  | cop  | table:t2, range:[-inf,+inf], keep order:false, stats:pseudo                                                                 |
|   └─IndexReader_18       | 0.01  | root | index:IndexScan_17                                                                                                          |
|     └─IndexScan_17       | 0.01  | cop  | table:t1, index:a, b, range: decided by [eq(test.t1.a, test.t2.a) lt(test.t1.b, test.t2.b)], keep order:false, stats:pseudo |
+--------------------------+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> desc select /*+ TIDB_HJ(t1) */ count(*) from t2 left join t1 on t1.a = t2.a and t1.b < t2.b;
+--------------------------+-------+------+--------------------------------------------------------------------------------------------------------------+
| id                       | count | task | operator info                                                                                                |
+--------------------------+-------+------+--------------------------------------------------------------------------------------------------------------+
| StreamAgg_11             | 1.00  | root | funcs:count(1)                                                                                               |
| └─HashLeftJoin_23        | 9.00  | root | left outer join, inner:IndexReader_19, equal:[eq(test.t2.a, test.t1.a)], other cond:lt(test.t1.b, test.t2.b) |
|   ├─TableReader_17       | 9.00  | root | data:TableScan_16                                                                                            |
|   │ └─TableScan_16       | 9.00  | cop  | table:t2, range:[-inf,+inf], keep order:false, stats:pseudo                                                  |
|   └─IndexReader_19       | 6.00  | root | index:IndexScan_18                                                                                           |
|     └─IndexScan_18       | 6.00  | cop  | table:t1, index:a, b, range:[NULL,+inf], keep order:false, stats:pseudo                                      |
+--------------------------+-------+------+--------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

But if we only use one condition on the inner side, we can get the same result.

mysql> desc select /*+ TIDB_INLJ(t1) */ count(*) from t2 left join t1 on t1.a = t2.a and t1.b+100-100 < t2.b;
+--------------------------+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| id                       | count | task | operator info                                                                                                                               |
+--------------------------+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_11             | 1.00  | root | funcs:count(1)                                                                                                                              |
| └─IndexJoin_19           | 9.00  | root | left outer join, inner:IndexReader_18, outer key:test.t2.a, inner key:test.t1.a, other cond:lt(minus(plus(test.t1.b, 100), 100), test.t2.b) |
|   ├─TableReader_16       | 9.00  | root | data:TableScan_15                                                                                                                           |
|   │ └─TableScan_15       | 9.00  | cop  | table:t2, range:[-inf,+inf], keep order:false, stats:pseudo                                                                                 |
|   └─IndexReader_18       | 0.01  | root | index:IndexScan_17                                                                                                                          |
|     └─IndexScan_17       | 0.01  | cop  | table:t1, index:a, b, range: decided by [eq(test.t1.a, test.t2.a)], keep order:false, stats:pseudo                                          |
+--------------------------+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> select /*+ TIDB_INLJ(t1) */ count(*) from t2 left join t1 on t1.a = t2.a and t1.b+100-100 < t2.b;
+----------+
| count(*) |
+----------+
|       17 |
+----------+
1 row in set (0.00 sec)

2. What did you expect to see?

IndexJoin should have the same results with HashJoin.

3. What did you see instead?

Wrong results returned by IndexJoin.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

release v3.0.8