Re: [PATCH] Combine qual-based and NOT NULL proofs when reducing outer joins

> I reviewed v1 of this patch. The approach looks sound to me.
>
> state->nullable_rels records whether a relation can be null-extended
> within the subtree in general. If find_nonnullable_rels(all_quals)
> proves that the relation cannot be an all-NULL row among the rows of
> interest, then it must represent a real base-table row there, so its
> table-level NOT NULL constraints can be used again.
Hi Ziming,

Thanks for taking the time to review and test this, and sorry for the
slow reply -- I was away for a few weeks.
Your summary is exactly the argument: nullable_rels answers "could this
rel ever be null-extended in the subtree?", while find_nonnullable_rels
over the collected quals answers the narrower question "could it be
null-extended in a row that survives these quals?". Only the second
question matters for the Vars we are trying to prove non-null, so the
NOT NULL constraints can be trusted again for rels where the answer is
no.

> I tested the patch with an --enable-debug --enable-cassert build. The
> core regression tests passed, and git diff --check reports no problems.
>
> I also compared patched and unpatched builds with a few additional
> cases. The strict cases were reduced to Anti Join with unchanged
> results, while a non-strict condition that allowed a lower
> null-extended row to pass correctly retained the outer join. I also
> checked the FULL JOIN case and did not find a result difference.
Glad the non-strict and FULL JOIN cases held up on your side as well;
those were the ones I was least sure I had covered.
I just re-checked v1 against current master (36a56229765) and it still
applies cleanly, so I am not posting a new version for now. Richard's
recent changes in this area (ad4b80b6f49 and e28cf96e935) are both
already included in the base I tested on.

Thanks again,
Zhenglong Li

Ziming Zhang <toren(dot)zhang(at)outlook(dot)com> 于2026年9月21日周一 00:16写道：

>
> Hi Zhenglong,
>
> I reviewed v1 of this patch. The approach looks sound to me.
>
> state->nullable_rels records whether a relation can be null-extended
> within the subtree in general. If find_nonnullable_rels(all_quals)
> proves that the relation cannot be an all-NULL row among the rows of
> interest, then it must represent a real base-table row there, so its
> table-level NOT NULL constraints can be used again.
>
> The new proof only considers the quals already collected by
> forced_null_var_is_nonnullable(), so it does not appear to weaken the
> existing safety rules for outer-join reduction.
>
> I tested the patch with an --enable-debug --enable-cassert build. The
> core regression tests passed, and git diff --check reports no problems.
>
> I also compared patched and unpatched builds with a few additional
> cases. The strict cases were reduced to Anti Join with unchanged
> results, while a non-strict condition that allowed a lower
> null-extended row to pass correctly retained the outer join. I also
> checked the FULL JOIN case and did not find a result difference.
>
> I did not find any correctness or implementation issues. The patch
> looks good to me.
>
> Regards,
> Ziming
>
> ________________________________________
> From: Keyerror Smart <smartkeyerror(at)gmail(dot)com>
> Sent: Tuesday, September 8, 2026 17:46
> To: PostgreSQL Hackers
> Subject: [PATCH] Combine qual-based and NOT NULL proofs when reducing
> outer joins
>
> Hi hackers,
>
> reduce_outer_joins() can convert a LEFT or FULL join to an anti-join
> when an upper qual forces a Var from the nullable side to be NULL while
> that Var is provably non-null in every row of interest. There are two
> proof methods: quals that hold for all such rows (the subtree's safe
> quals, plus the join's own ON quals in the LEFT case) can prove specific
> Vars non-null, and a NOT NULL table constraint can prove a Var non-null
> unless its rel can be nulled by lower-level outer joins within the
> subtree.
>
> Previously the two methods did not combine: once a rel was found
> nullable within the subtree, its NOT NULL constraints were ignored
> outright. But if the collected quals are strict for the rel, it cannot
> have been null-extended in any row of interest, so its NOT NULL
> constraints do apply to those rows after all. Compute
> find_nonnullable_rels() over the quals forced_null_var_is_nonnullable()
> already collects, and have forced_null_var_is_attnotnull() trust NOT
> NULL constraints of rels so proven.
>
> For example, with Q.ts declared NOT NULL, this query's outer join can
> now be reduced to an anti-join: the upper ON clause Q.f = 1 is strict
> for Q, so Q cannot be null-extended in any matching row, leaving the
> upper join's own null-extension as the only way to satisfy the WHERE
> clause.
>
> SELECT * FROM R LEFT JOIN (S LEFT JOIN Q ON S.c = Q.e)
> ON R.a = S.c AND Q.f = 1
> WHERE Q.ts IS NULL;
>
> The same reasoning applies at a FULL join, where safe quals within one
> input can prove a rel non-extended in every row that input emits.
>
> Patch atrtached.
>
> Regards,
> Zhenglong Li
>
>