Re: Wrong results with right-semi-joins

On Mon, Dec 9, 2024 at 11:01 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
> 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

Upon further consideration, I believe the initplan is unnecessary.
What we really want from the plan is to reuse the hash table during
hash-right-semi-join rescans. To achieve this, we just need to ensure
that it's a single-batch join and that there are no parameter changes
on the inner side.

I spent some time on this and came up with a simpler query to
reproduce the issue.

explain (costs off)
select * from tbl_rs t1 join
lateral (select * from tbl_rs t2 where t2.a in
(select t1.a+t3.a from tbl_rs t3) and t2.a < 5)
on true;
QUERY PLAN
-------------------------------------------
Nested Loop
-> Seq Scan on tbl_rs t1
-> Hash Right Semi Join
Hash Cond: ((t1.a + t3.a) = t2.a)
-> Seq Scan on tbl_rs t3
-> Hash
-> Seq Scan on tbl_rs t2
Filter: (a < 5)
(8 rows)

Without the fix, this query returns 3 rows rather than the expected 6.

Maybe I should update the test case introduced in 5668a857d to this
one.

Thanks
Richard