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

> On Nov 2, 2026, at 15:51, Ziming Zhang <toren(dot)zhang(at)outlook(dot)com> wrote:
>
> Hi Zhenglong,
>
> I noticed that the [Windows CI job on CFBot](https://github.com/postgres/postgres-cfbot/actions/runs/41330725300/job/124920407235) failed with “The hosted runner lost communication with the server.”
>
> I suggest checking the failure and, if it appears to be a runner issue, and requeuing the tests from the CommitFest page.
>
> Regards,
> Ziming
>
> ________________________________________
> From: Keyerror Smart <smartkeyerror(at)gmail(dot)com>
> Sent: Monday, October 12, 2026 13:44
> To: Ziming Zhang
> Cc: PostgreSQL Hackers
> Subject: 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<mailto: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<mailto: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

Sorry for jumping in. I haven’t reviewed this patch. Just a hint for top-posting. Bottom-posting is the preferred style on this mailing list, meaning that, when you reply to an email, put the new content below the quoted content. Some email client allows to configure that, like MacOS’s built-in Mail app.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/