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

On Wed, Dec 11, 2024 at 11:27 AM Richard Guo <guofenglinux@gmail.com> wrote:
> 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.

Done.

Thanks
Richard