Subject: Wrong results with right-semi-joins

From: Richard Guo <[email-masked]>
Date: Tue, 3 Dec 2024 17:56:17 +0900

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

---

From: Melanie Plageman <[email-masked]>
Date: Mon, 9 Dec 2024 09:01:27 -0500

> The expected output should be 10 rows, not 1.

Thanks for finding and fixing this. Just for my own benefit, could you
explain more about the minimal repro? Specifically, if you just need a
subplan in the hash side of a right semi-join, why do you also need
the outer part of the query that produces the initplan?

 Seq Scan on tbl_rs t1
   Filter: ((SubPlan 3) >= 0)
   SubPlan 3
     ->  Limit
           InitPlan 2
             ->  Hash Right Semi Join

- Melanie