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

On Tue, Dec 3, 2024 at 3:56 AM Richard Guo <guofenglinux@gmail.com> wrote:
>
> 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.

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