| From: | Tomas Vondra <tomas(dot)vondra(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-committers(at)lists(dot)postgresql(dot)org |
| Subject: | pgsql: Fix FULL JOIN constant propagation with non-strict operands |
| Date: | 2026-11-19 14:35:44 |
| Message-ID: | E1xU3El-00000000Oce-3Ocr@gemulon.postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-committers |
Fix FULL JOIN constant propagation with non-strict operands
Deriving input restrictions from COALESCE(left, right) = constant can
discard unmatched rows when either operand produces a non-NULL value
for a null-extended input.
Require both operands to be provably null-preserving before deriving
either equality, so that the value remains NULL for null-extended input.
An expression is null-preserving if it contains only strict functions,
and at least one level-zero variable (Var or PlaceHolderVar).
The check is implemented as an expression walker, closely mirrorring
contain_nonstrict_functions_walker(), with different handling for Var
and PlaceHolderVar at level zero, and CoalesceExpr at any depth. The
walker does not descend into level-zero PlaceHolderVars, as those are
evaluated below the join and so go to NULL for null-extended rows.
CoalesceExpr is treated as null-preserving if all its arguments are
null-preserving. So for example (when K is a constant)
COALESCE(left.a, COALESCE(right.a, right.b)) = K
is null-preserving, while
COALESCE(left.a, COALESCE(right.a, K)) = K
is not, because the nested COALESCE discards NULL values.
PlaceHolderVars at level zero are automatically null-preserving. Those
represent expressions calculated below the join, so the result will be
nulled no matter the expression.
Add regression coverage for unsafe operands and safe nested FULL JOIN
USING cases.
Backpatch to all supported branches. The issue was likely introduced in
8.1, by commit cc5e80b8d1c4.
Author: Tomas Vondra <tomas(at)vondra(dot)me>
Reviewed-by: Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com>
Reviewed-by: Kirill Reshke <reshkekirill(at)gmail(dot)com>
Discussion: https://postgr.es/m/0074fbf7-a92c-417a-937a-6b1fc27aed82@vondra.me
Backpatch-through: 15
Branch
------
REL_15_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/b41a24a3188037ab9e3083edf3c44dbcbd50d229
Modified Files
--------------
src/backend/optimizer/path/equivclass.c | 9 ++
src/backend/optimizer/util/clauses.c | 195 ++++++++++++++++++++++++++++++++
src/include/optimizer/clauses.h | 1 +
src/test/regress/expected/join.out | 138 ++++++++++++++++++++++
src/test/regress/sql/join.sql | 58 ++++++++++
5 files changed, 401 insertions(+)
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Nathan Bossart | 2026-11-19 15:21:08 | pgsql: Fix race in autovacuum freeze score calculations. |
| Previous Message | Tomas Vondra | 2026-11-19 14:35:36 | pgsql: Fix FULL JOIN constant propagation with non-strict operands |