Reduce outer joins to anti joins for whole-row IS NULL tests reduce_outer_joins() recognizes "WHERE b.z IS NULL" above an outer join as an anti-join condition, but not the whole-row "WHERE b IS NULL", which is a natural way to ask for an anti-join without naming a specific column. Teach it to recognize the whole-row form too.
A row that the join null-extends has all of b's columns set to NULL, so it satisfies "b IS NULL". A matched row satisfies the test only if its columns happen to be all NULL. Hence proving any one column of b non-null in matching rows rules out every matched row, leaving only null-extended rows: exactly anti-join semantics. This mirrors the single-column case, and the same proofs apply: a NOT NULL table constraint, a strict join clause (for LEFT joins), or strict quals within the relation's subtree. Because any one column suffices, the whole-row test reduces in a strict superset of the cases a single-column test does. To implement this, find_forced_null_vars() now reports a whole-row Var tested with row-format IS NULL as a varattno-zero entry meaning that all of the relation's columns are forced null. Row-format tests on ordinary composite-type columns remain excluded, since such a test does not force the column null: it is also true when the column is a non-null row whose fields are all NULL. The proof functions in reduce_outer_joins() treat a whole-row entry accordingly: it is refuted by proving any one column of its relation non-null. A match on the whole-row attribute itself proves nothing, because a non-null composite datum can still have all columns NULL, so the per-column matching now explicitly excludes that attribute. Author: Richard Guo <[email protected]> Reviewed-by: wenhui qiu <[email protected]> Discussion: https://postgr.es/m/cambws49h9khf+1gwyzd0tyaks6ce-ou+1mbuion3gzsobio...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/02119d6c87e20bd17f9b94b0420c2713d62275bc Modified Files -------------- src/backend/optimizer/prep/prepjointree.c | 101 +++++++++++++++---- src/backend/optimizer/util/clauses.c | 19 +++- src/test/regress/expected/join.out | 156 ++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 60 ++++++++++++ 4 files changed, 314 insertions(+), 22 deletions(-)
