Re: Wrong results with right-semi-joins - Mailing list pgsql-hackers

On Mon, Dec 9, 2024 at 11:01 PM Melanie Plageman
<melanieplageman@gmail.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