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

Hi,

On Fri, Oct 16, 2026 at 6:41 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
>
> PG Bug reporting form <noreply(at)postgresql(dot)org> 于2026年10月16日周五 17:50写道：
> >
> > EXPLAIN shows that all three join conditions have disappeared, leaving
> > three unqualified nested-loop left joins:
> >
> > Nested Loop Left Join
> > -> Values Scan on "*VALUES*"
> > Filter: (column1 = 1)
> > -> Nested Loop Left Join
> > -> Nested Loop Left Join
> > -> Values Scan on "*VALUES*_1"
> > Filter: (column1 = 1)
> > -> Values Scan on "*VALUES*_2"
> > Filter: (column1 = 1)
> > -> Values Scan on "*VALUES*_3"
> > Filter: (column1 = 1)
>
> I think the problem is in the interaction between constant propagation
> in reconsider_outer_join_clauses() and outer-join identity 3.
>
Thanks for the analysis. I also found the same cause with LLM help.

>
> I am considering guarding this optimization when a commuting lower
> outer join could null-extend the outer operand.
> Specifically, I would examine the joins in sjinfo->commute_below_l and
> retain the original qual if one of their syn_righthand sets overlaps
> the outer operand's relids.
>

It seems that this might add an extra filter for a query like this

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;

I considered instead keeping the qual if the clone is incompatible
with some other outer join whose commute_below_l is non-empty. It
seemed like a narrower check that should preserve the optimization in
the above scenario.

I'm attaching the patch with this idea. Please take a look and let me
know what you think.

Thanks & Regards,
Nitin Motiani
Google