IndexHashJoin may produce redundant rows for anti semi left outer join #52902
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
Because it is difficult to generate IndexHashJoin executor, we need to hack the tidb planner code to enforce it generate IndexHashJoin executor using the attached hack.txt:
hack.txt
Then using the hacked tidb, we can run sqls:
use test;
create table t1 (x int, y int);
create table t0 (a int, b int, key (b));
insert into t1 values(103, 600);
insert into t1 values(100, 200);
insert into t0 values( 105, 400);
insert into t0 values( 104, 300);
insert into t0 values( 103, 300);
insert into t0 values( 102, 200);
insert into t0 values( 101, 200);
insert into t0 values( 100, 200);
select * from t1 where 1 = 1 and case when t1.x < 1000 then 1 = 1 when t1.x < 2000 then not exists (select 1 from t0 where t0.b = t1.y) else 1 = 1 end;
2. What did you expect to see? (Required)
+------+------+
| 100 | 200 |
| 103 | 600 |
+------+------+
3. What did you see instead (Required)
+------+------+
| 100 | 200 |
| 100 | 200 |
| 100 | 200 |
| 103 | 600 |
+------+------+
4. What is your TiDB version? (Required)
| Release Version: v8.1.0-alpha-143-g111abf32b4-dirty
Edition: Community
Git Commit Hash: 111abf3
Git Branch: mpp_join_issue_52828
UTC Build Time: 2024-04-26 03:20:49
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: tikv |