IndexMergeJoin on a partitioned table causes wrong result

[zyguan]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1, t2;

create table t1  (c_int int, c_str varchar(40), primary key (c_int) ) partition by range (c_int) ( partition p0 values less than (10), partition p1 values less than maxvalue );
create table t2  (c_int int, c_str varchar(40), primary key (c_int) ) partition by range (c_int) ( partition p0 values less than (10), partition p1 values less than maxvalue );
insert into t1 values (1, 'Alice');
insert into t2 values (1, 'Bob');

select /*+ HASH_JOIN(t1,t2) */ * from t1 join t2 on t1.c_int = t2.c_int and t1.c_str < t2.c_str;
select /*+ INL_MERGE_JOIN(t1,t2) */ * from t1 join t2 on t1.c_int = t2.c_int and t1.c_str < t2.c_str;

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

Both selection should return

+-------+-------+-------+-------+
| c_int | c_str | c_int | c_str |
+-------+-------+-------+-------+
|     1 | Alice |     1 | Bob   |
+-------+-------+-------+-------+

3. What did you see instead (Required)

mysql> select /*+ HASH_JOIN(t1,t2) */ * from t1 join t2 on t1.c_int = t2.c_int and t1.c_str < t2.c_str;
+-------+-------+-------+-------+
| c_int | c_str | c_int | c_str |
+-------+-------+-------+-------+
|     1 | Alice |     1 | Bob   |
+-------+-------+-------+-------+
1 row in set (0.06 sec)

mysql> select /*+ INL_MERGE_JOIN(t1,t2) */ * from t1 join t2 on t1.c_int = t2.c_int and t1.c_str < t2.c_str;
Empty set (0.05 sec)

Execute select /*+ INL_MERGE_JOIN(t1,t2) */ * from t1 partition (p0) join t2 on t1.c_int = t2.c_int and t1.c_str < t2.c_str; to reproduce the issue on release-4.0 .

4. What is your TiDB version? (Required)

master @ ae779e1
release-4.0 @ 3948c7b

[liuzix]

/label sig/execution

[lzmhhh123]

@zyguan Are you sure you produce this bug on release-4.0 branch? In TiDB 4.0, the partition table can't be the inner child of index merge join. I can produce it on master branch, it's not a critical issue. And the root cause may be #18981. @tiancaiamao Please take a look.

[zyguan]

@lzmhhh123 try select /*+ INL_MERGE_JOIN(t1,t2) */ * from t1 partition (p0) join t2 on t1.c_int = t2.c_int and t1.c_str < t2.c_str; or select /*+ INL_MERGE_JOIN(t1,t2) */ * from t1 join t2 on t1.c_int = t2.c_int and t1.c_str < t2.c_str and t1.c_int in (1, 2, 3);

[lzmhhh123]

Ok, that's a bug, it seems index join, index hash join and index merge join have the same problem.

[tiancaiamao]

Reopen because the fix is only for 4.0, I'm working on the fix for master.