Hi, Here's a rebased version of the patch(es). I've made no change to the code itself, I've tried to improve the commit message to explain why the expressions need to be null-preserving, handling of COALESCE clauses and placeholders.
Initially I've planned to backpatch to 16, as I assumed this was likely introduced by the JoinDomain improvements in that release. But it seems it's actually much older, possibly even going all the way back to 8.1 (but I haven't tried to build that). All I know is that the regression tests give incorrect results on 15 (without the code fix). regards -- Tomas Vondra
From 8544b9135274e32216f380328b0c485444008442 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Wed, 11 Nov 2026 22:38:20 +0100 Subject: [PATCH v2-15] 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). Nested COALESCE clauses and PlaceHolderVar expressions are handled explicitly. COALESCE is null-preserving iff all it's arguments are. So for example 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 <[email protected]> Reviewed-by: Ayush Tiwari <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 15 --- src/backend/optimizer/path/equivclass.c | 45 +++++++++++++ src/test/regress/expected/join.out | 87 +++++++++++++++++++++++++ src/test/regress/sql/join.sql | 37 +++++++++++ 3 files changed, 169 insertions(+) diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index b15f6603dd6..1e10f393b7e 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -64,6 +64,7 @@ static bool reconsider_outer_join_clause(PlannerInfo *root, bool outer_on_left); static bool reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo); +static bool is_full_join_expr_null_preserving(Node *node); static Bitmapset *get_eclass_indexes_for_relids(PlannerInfo *root, Relids relids); static Bitmapset *get_common_eclass_indexes(PlannerInfo *root, Relids relids1, @@ -2258,6 +2259,15 @@ reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo) bms_is_subset(right_relids, sjinfo->syn_lefthand))) return false; + /* + * For an unmatched row, the other input's expression must become NULL. + * Otherwise COALESCE could supply the constant even when this input's + * expression is different, making the derived restriction invalid. + */ + if (!is_full_join_expr_null_preserving((Node *) leftvar) || + !is_full_join_expr_null_preserving((Node *) rightvar)) + return false; + foreach(lc1, root->eq_classes) { EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1); @@ -2394,6 +2404,41 @@ reconsider_full_join_clause(PlannerInfo *root, RestrictInfo *rinfo) } +/* + * Check whether an expression becomes NULL when its FULL JOIN input is + * null-extended. The caller has verified that all referenced rels belong + * to one input. + * + * A PlaceHolderVar is evaluated below the join, so its result is nulled + * regardless of the contained expression. COALESCE is not strict in any + * one argument, but preserves NULL if all its arguments do. + * + * Otherwise, conservatively require strictness and at least one level-zero + * Var or PlaceHolderVar. The latter test rules out non-NULL constants and + * pseudoconstants appearing as COALESCE arguments. + */ +static bool +is_full_join_expr_null_preserving(Node *node) +{ + if (IsA(node, PlaceHolderVar)) + return ((PlaceHolderVar *) node)->phlevelsup == 0; + if (IsA(node, CoalesceExpr)) + { + CoalesceExpr *cexpr = (CoalesceExpr *) node; + ListCell *lc; + + foreach(lc, cexpr->args) + { + if (!is_full_join_expr_null_preserving(lfirst(lc))) + return false; + } + return true; + } + + return !contain_nonstrict_functions(node) && + contain_vars_of_level(node, 0); +} + /* * exprs_known_equal * Detect whether two expressions are known equal due to equivalence diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 7c68e4bd2b6..58fd904e225 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6653,6 +6653,93 @@ ERROR: invalid reference to FROM-clause entry for table "xx1" LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss; ^ HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query. +-- non-strict operands must not cause unmatched rows to be filtered out +explain (costs off) +select * from (values (null::int), (1)) a(x) + full join (values (0), (1)) b(x) on a.x = coalesce(b.x, 0) + where coalesce(a.x, coalesce(b.x, 0)) = 0 + order by a.x, b.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Sort Key: "*VALUES*".column1, "*VALUES*_1".column1 + -> Hash Full Join + Hash Cond: ("*VALUES*".column1 = COALESCE("*VALUES*_1".column1, 0)) + Filter: (COALESCE("*VALUES*".column1, COALESCE("*VALUES*_1".column1, 0)) = 0) + -> Values Scan on "*VALUES*" + -> Hash + -> Values Scan on "*VALUES*_1" +(8 rows) + +select * from (values (null::int), (1)) a(x) + full join (values (0), (1)) b(x) on a.x = coalesce(b.x, 0) + where coalesce(a.x, coalesce(b.x, 0)) = 0 + order by a.x, b.x; + x | x +---+--- + | 0 + | +(2 rows) + +explain (costs off) +select * from (values (0), (1)) a(x) + full join (values (null::int), (1)) b(x) + on (case when a.x is null then 0 else a.x end) = b.x + where coalesce(case when a.x is null then 0 else a.x end, b.x) = 0 + order by a.x, b.x; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------- + Sort + Sort Key: "*VALUES*".column1, "*VALUES*_1".column1 + -> Hash Full Join + Hash Cond: (CASE WHEN ("*VALUES*".column1 IS NULL) THEN 0 ELSE "*VALUES*".column1 END = "*VALUES*_1".column1) + Filter: (COALESCE(CASE WHEN ("*VALUES*".column1 IS NULL) THEN 0 ELSE "*VALUES*".column1 END, "*VALUES*_1".column1) = 0) + -> Values Scan on "*VALUES*" + -> Hash + -> Values Scan on "*VALUES*_1" +(8 rows) + +select * from (values (0), (1)) a(x) + full join (values (null::int), (1)) b(x) + on (case when a.x is null then 0 else a.x end) = b.x + where coalesce(case when a.x is null then 0 else a.x end, b.x) = 0 + order by a.x, b.x; + x | x +---+--- + 0 | + | +(2 rows) + +-- nested merged columns and precomputed expressions remain null-preserving +explain (costs off) +select * from (select coalesce(f1, 0) as f1 from int4_tbl) a + full join int4_tbl b using (f1) + full join int4_tbl c using (f1) + where f1 = 0; + QUERY PLAN +---------------------------------------------------------------------------- + Hash Full Join + Hash Cond: (COALESCE((COALESCE(int4_tbl.f1, 0)), b.f1) = c.f1) + Filter: (COALESCE(COALESCE((COALESCE(int4_tbl.f1, 0)), b.f1), c.f1) = 0) + -> Hash Full Join + Hash Cond: ((COALESCE(int4_tbl.f1, 0)) = b.f1) + -> Seq Scan on int4_tbl + -> Hash + -> Seq Scan on int4_tbl b + -> Hash + -> Seq Scan on int4_tbl c + Filter: (f1 = 0) +(11 rows) + +select * from (select coalesce(f1, 0) as f1 from int4_tbl) a + full join int4_tbl b using (f1) + full join int4_tbl c using (f1) + where f1 = 0; + f1 +---- + 0 +(1 row) + -- -- test LATERAL reference propagation down a multi-level inheritance hierarchy -- produced for a multi-level partitioned table hierarchy. diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index affe1c4cbaf..4dc444ee52c 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2313,6 +2313,43 @@ delete from xx1 using (select * from int4_tbl where f1 = x1) ss; delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss; delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss; +-- non-strict operands must not cause unmatched rows to be filtered out +explain (costs off) +select * from (values (null::int), (1)) a(x) + full join (values (0), (1)) b(x) on a.x = coalesce(b.x, 0) + where coalesce(a.x, coalesce(b.x, 0)) = 0 + order by a.x, b.x; + +select * from (values (null::int), (1)) a(x) + full join (values (0), (1)) b(x) on a.x = coalesce(b.x, 0) + where coalesce(a.x, coalesce(b.x, 0)) = 0 + order by a.x, b.x; + +explain (costs off) +select * from (values (0), (1)) a(x) + full join (values (null::int), (1)) b(x) + on (case when a.x is null then 0 else a.x end) = b.x + where coalesce(case when a.x is null then 0 else a.x end, b.x) = 0 + order by a.x, b.x; + +select * from (values (0), (1)) a(x) + full join (values (null::int), (1)) b(x) + on (case when a.x is null then 0 else a.x end) = b.x + where coalesce(case when a.x is null then 0 else a.x end, b.x) = 0 + order by a.x, b.x; + +-- nested merged columns and precomputed expressions remain null-preserving +explain (costs off) +select * from (select coalesce(f1, 0) as f1 from int4_tbl) a + full join int4_tbl b using (f1) + full join int4_tbl c using (f1) + where f1 = 0; + +select * from (select coalesce(f1, 0) as f1 from int4_tbl) a + full join int4_tbl b using (f1) + full join int4_tbl c using (f1) + where f1 = 0; + -- -- test LATERAL reference propagation down a multi-level inheritance hierarchy -- produced for a multi-level partitioned table hierarchy. -- 2.56.0
From d40037997daf353547780bf13a615c26d77862f8 Mon Sep 17 00:00:00 2001 From: Tomas Vondra <[email protected]> Date: Wed, 11 Nov 2026 20:47:24 +0100 Subject: [PATCH v2-master] 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). Nested COALESCE clauses and PlaceHolderVar expressions are handled explicitly. COALESCE is null-preserving iff all it's arguments are. So for example 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 <[email protected]> Reviewed-by: Ayush Tiwari <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 15 --- src/backend/optimizer/path/equivclass.c | 45 +++++++++++++ src/test/regress/expected/join.out | 86 +++++++++++++++++++++++++ src/test/regress/sql/join.sql | 37 +++++++++++ 3 files changed, 168 insertions(+) diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index 439cb651c01..87a8f4ca495 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -80,6 +80,7 @@ static bool reconsider_outer_join_clause(PlannerInfo *root, bool outer_on_left); static bool reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo); +static bool is_full_join_expr_null_preserving(Node *node); static JoinDomain *find_join_domain(PlannerInfo *root, Relids relids); static Bitmapset *get_eclass_indexes_for_relids(PlannerInfo *root, Relids relids); @@ -2481,6 +2482,15 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo) else return false; + /* + * For an unmatched row, the other input's expression must become NULL. + * Otherwise COALESCE could supply the constant even when this input's + * expression is different, making the derived restriction invalid. + */ + if (!is_full_join_expr_null_preserving((Node *) leftvar) || + !is_full_join_expr_null_preserving((Node *) rightvar)) + return false; + foreach(lc1, root->eq_classes) { EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1); @@ -2633,6 +2643,41 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo) return false; /* failed to make any deduction */ } +/* + * Check whether an expression becomes NULL when its FULL JOIN input is + * null-extended. The caller has verified that all referenced rels belong + * to one input. + * + * A PlaceHolderVar is evaluated below the join, so its result is nulled + * regardless of the contained expression. COALESCE is not strict in any + * one argument, but preserves NULL if all its arguments do. + * + * Otherwise, conservatively require strictness and at least one level-zero + * Var or PlaceHolderVar. The latter test rules out non-NULL constants and + * pseudoconstants appearing as COALESCE arguments. + */ +static bool +is_full_join_expr_null_preserving(Node *node) +{ + if (IsA(node, PlaceHolderVar)) + return ((PlaceHolderVar *) node)->phlevelsup == 0; + if (IsA(node, CoalesceExpr)) + { + CoalesceExpr *cexpr = (CoalesceExpr *) node; + ListCell *lc; + + foreach(lc, cexpr->args) + { + if (!is_full_join_expr_null_preserving(lfirst(lc))) + return false; + } + return true; + } + + return !contain_nonstrict_functions(node) && + contain_vars_of_level(node, 0); +} + /* * find_join_domain * Find the highest JoinDomain enclosed within the given relid set. diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 526430a5ea0..896fc646347 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6556,6 +6556,92 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1, 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 (10 rows) +-- non-strict operands must not cause unmatched rows to be filtered out +explain (costs off) +select * from (values (null::int), (1)) a(x) + full join (values (0), (1)) b(x) on a.x = coalesce(b.x, 0) + where coalesce(a.x, coalesce(b.x, 0)) = 0 + order by a.x, b.x; + QUERY PLAN +--------------------------------------------------------------------------------------- + Sort + Sort Key: "*VALUES*".column1, "*VALUES*_1".column1 + -> Hash Full Join + Hash Cond: ("*VALUES*".column1 = COALESCE("*VALUES*_1".column1, 0)) + Filter: (COALESCE("*VALUES*".column1, COALESCE("*VALUES*_1".column1, 0)) = 0) + -> Values Scan on "*VALUES*" + -> Hash + -> Values Scan on "*VALUES*_1" +(8 rows) + +select * from (values (null::int), (1)) a(x) + full join (values (0), (1)) b(x) on a.x = coalesce(b.x, 0) + where coalesce(a.x, coalesce(b.x, 0)) = 0 + order by a.x, b.x; + x | x +---+--- + | 0 + | +(2 rows) + +explain (costs off) +select * from (values (0), (1)) a(x) + full join (values (null::int), (1)) b(x) + on (case when a.x is null then 0 else a.x end) = b.x + where coalesce(case when a.x is null then 0 else a.x end, b.x) = 0 + order by a.x, b.x; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------- + Sort + Sort Key: "*VALUES*".column1, "*VALUES*_1".column1 + -> Hash Full Join + Hash Cond: (CASE WHEN ("*VALUES*".column1 IS NULL) THEN 0 ELSE "*VALUES*".column1 END = "*VALUES*_1".column1) + Filter: (COALESCE(CASE WHEN ("*VALUES*".column1 IS NULL) THEN 0 ELSE "*VALUES*".column1 END, "*VALUES*_1".column1) = 0) + -> Values Scan on "*VALUES*" + -> Hash + -> Values Scan on "*VALUES*_1" +(8 rows) + +select * from (values (0), (1)) a(x) + full join (values (null::int), (1)) b(x) + on (case when a.x is null then 0 else a.x end) = b.x + where coalesce(case when a.x is null then 0 else a.x end, b.x) = 0 + order by a.x, b.x; + x | x +---+--- + 0 | + | +(2 rows) + +-- nested merged columns and precomputed expressions remain null-preserving +explain (costs off) +select * from (select coalesce(f1, 0) as f1 from int4_tbl) a + full join int4_tbl b using (f1) + full join int4_tbl c using (f1) + where f1 = 0; + QUERY PLAN +--------------------------------------------- + Merge Full Join + -> Merge Full Join + -> Seq Scan on int4_tbl + Filter: (COALESCE(f1, 0) = 0) + -> Materialize + -> Seq Scan on int4_tbl b + Filter: (f1 = 0) + -> Materialize + -> Seq Scan on int4_tbl c + Filter: (f1 = 0) +(10 rows) + +select * from (select coalesce(f1, 0) as f1 from int4_tbl) a + full join int4_tbl b using (f1) + full join int4_tbl c using (f1) + where f1 = 0; + f1 +---- + 0 +(1 row) + -- -- check deductions from FULL JOIN clauses and COALESCE() = constant: the -- join clause's operands may appear in either order, but must come from diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 27b44016717..61b897e1eb1 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2278,6 +2278,43 @@ select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1, select * from int8_tbl t1 left join int8_tbl t2 on t1.q2 = t2.q1, lateral (select * from int8_tbl t3 where t2.q1 = t2.q2) ss; +-- non-strict operands must not cause unmatched rows to be filtered out +explain (costs off) +select * from (values (null::int), (1)) a(x) + full join (values (0), (1)) b(x) on a.x = coalesce(b.x, 0) + where coalesce(a.x, coalesce(b.x, 0)) = 0 + order by a.x, b.x; + +select * from (values (null::int), (1)) a(x) + full join (values (0), (1)) b(x) on a.x = coalesce(b.x, 0) + where coalesce(a.x, coalesce(b.x, 0)) = 0 + order by a.x, b.x; + +explain (costs off) +select * from (values (0), (1)) a(x) + full join (values (null::int), (1)) b(x) + on (case when a.x is null then 0 else a.x end) = b.x + where coalesce(case when a.x is null then 0 else a.x end, b.x) = 0 + order by a.x, b.x; + +select * from (values (0), (1)) a(x) + full join (values (null::int), (1)) b(x) + on (case when a.x is null then 0 else a.x end) = b.x + where coalesce(case when a.x is null then 0 else a.x end, b.x) = 0 + order by a.x, b.x; + +-- nested merged columns and precomputed expressions remain null-preserving +explain (costs off) +select * from (select coalesce(f1, 0) as f1 from int4_tbl) a + full join int4_tbl b using (f1) + full join int4_tbl c using (f1) + where f1 = 0; + +select * from (select coalesce(f1, 0) as f1 from int4_tbl) a + full join int4_tbl b using (f1) + full join int4_tbl c using (f1) + where f1 = 0; + -- -- check deductions from FULL JOIN clauses and COALESCE() = constant: the -- join clause's operands may appear in either order, but must come from -- 2.56.0
