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

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.

To support join reordering, deconstruct_distribute_oj_quals()
generates variants of the join quals with different nullingrels
markings.
The least-nulled variants allow reconsider_outer_join_clause() to
propagate the constant through the chain: a=1 implies a restriction
b=1, then c=1, and finally d=1.
The corresponding join quals are replaced with constant-TRUE dummy clauses.

However, a restriction on the scan of C does not guarantee that c=1
still holds after C has been null-extended. In the chosen join order,

A LEFT JOIN ((B LEFT JOIN C) LEFT JOIN D)

the B/C join produces a row with c=NULL, since no C row passes c=1.
The original c=d qual would reject a match with D, whereas its TRUE
replacement allows d=1 to match.

Thus, deriving the inner-side restriction is not sufficient to
establish that the join qual is redundant.
In particular, replacing a strict qual with TRUE can invalidate the
reasoning that allowed the outer joins to commute.

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 might also be possible to retain constant propagation while keeping
the original join qual, but that would require separating the
deduction from the decision to discard the qual.

--
Thanks,
Tender Wang