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

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