Re: BUG #19765: Wrong result from a chain of LEFT JOINs since v16

On Fri, Oct 16, 2026 at 8:34 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>
> I think we can fix this by only making the TRUE dummy for clauses that
> are not clones. For a clone, just throw the original clause back, as
> v15 and earlier did.
>

I had first tried this change and it worked but for one of the tests
in join.sql, it also added a redundant filter.

On Fri, Oct 16, 2026 at 8:13 PM Nitin Motiani <nitinmotiani(at)google(dot)com> wrote:
>
> select b.b, c.c, d.d from (values (1),(2)) as b(b)
> left join (values (7),(2)) as c(c) on b.b = c.c
> left join (values (1),(2)) as d(d) on c.c = d.d
> where b.b = 1;
>

And for the 3-join query like this, it looks like we will have an
extra filter. So in my patch [1], I skipped the TRUE dummy option if
the clone is incompatible with some other outer join whose
commute_below_l is non-empty as only such an outer join can have its
relid deferred (add_outer_joins_to_relids). That approach also seems
to work and avoids adding the extra filter in the above scenario.

Let me know what you think of this approach compared to the simpler
solution of removing the TRUE dummy for all the cases with clones.

Thanks,
Nitin Motiani
Google

[1] https://www.postgresql.org/message-id/CAH5HC94V8kr1OcSDkzh2kGTNE6PU3ZXeF_G1LZ76BnE0E-9gnQ%40mail.gmail.com