Wrong results with right-semi-joins
Richard Guo <guofenglinux@gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2024-12-03T08:56:17Z
Lists: pgsql-hackers
Attachments
- v1-0001-Fix-right-semi-joins-in-HashJoin-rescans.patch (application/octet-stream) patch v1-0001
I ran into $subject and it can be reproduced with the query below.
create temp table tbl_rs(a int, b int);
insert into tbl_rs select i, i from generate_series(1,10)i;
analyze tbl_rs;
set enable_nestloop to off;
set enable_hashagg to off;
select * from tbl_rs t1
where (select a from tbl_rs t2
where exists (select 1 from
(select (b in (select b from tbl_rs t3)) as c
from tbl_rs t4 where t4.a = 1) s
where c in
(select t1.a = 1 from tbl_rs t5 union all select true))
order by a limit 1) >= 0;
a | b
---+---
1 | 1
(1 row)
The expected output should be 10 rows, not 1.
I've traced the root cause to ExecReScanHashJoin, where we neglect to
reset the inner-tuple match flags in the hash table for right-semi
joins when reusing the hash table. It was my oversight in commit
aa86129e1. Attached is patch to fix it.
Thanks
Richard
Commits
-
Improve the test case from 5668a857d
- d8f335156c57 18.0 landed
-
Fix right-semi-joins in HashJoin rescans
- 5668a857de4f 18.0 landed
-
Support "Right Semi Join" plan shapes
- aa86129e19d7 18.0 cited