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
v1-0001-Combine-qual-based-and-NOT-NULL-proofs-when-reduc.patch
Description: Binary data
