Hi,
While working on [1], an AI review pointed out a separate long-standing
bug in reconsider_full_join_clause(). Consider a query like this:
SELECT *
FROM t1 FULL JOIN t2 ON (t1.id = t2.id)
WHERE COALESCE(t1.id, t2.id) = 12345
The function correctly replaces the COALESCE() clause with two clauses
pushed down to the join inputs, and removes the join clause, as if
SELECT *
FROM (SELECT * FROM t1 WHERE t1.id = 12345)
FULL JOIN (SELECT * FROM t2 WHERE t2.id = 12345)
So far all good - such transformation is correct (this example does not
even need fixes from [1]). But the coalesce arguments may not be plain
Vars. Consider this instead:
SELECT *
FROM t1 FULL JOIN t2 ON (t1.id = COALESCE(t2.id, 12345))
WHERE COALESCE(t1.id, COALESCE(t2.id, 12345)) = 12345
Well, reconsider_full_join_clause() still pushed down the clauses to the
join inputs etc. as if
SELECT *
FROM (SELECT * FROM t1 WHERE t1.id = 12345)
FULL JOIN (SELECT * FROM t2 WHERE COALESCE(t2.id,12345) = 12345)
But this is wrong, because the COALESCE() gets rid of NULLs. If t2.id is
NULL ("id" does not need to be a PK), the row incorrectly matches. A
trivial example:
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (NULL);
INSERT INTO t2 VALUES (NULL);
SELECT t1.id as x, t2.id as y
FROM t1 FULL JOIN t2 ON (t1.id = COALESCE(t2.id, 12345))
WHERE COALESCE(t1.id, COALESCE(t2.id, 12345)) = 12345;
x | y
---+---
|
(1 row)
-- OFFSET 0 to block the pushdown, result should not change
SELECT * FROM (
SELECT t1.id as x, t2.id as y
FROM t1 FULL JOIN t2 ON (t1.id = COALESCE(t2.id, 12345)) OFFSET 0
) foo WHERE COALESCE(foo.x, COALESCE(foo.y, 12345)) = 12345;
x | y
---+---
|
|
(2 rows)
COALESCE is not the only way to get this issue, CASE can have the same
effect (example in the patch). There might be more ways to hit it ...
This requires the t1.id = COALESCE(t2.id, 12345) join clause, it works
OK without that. IIUC this is because it establishes a matching EC,
which is necessary for the misbehavior.
This query pattern (with COALESCE in the join clause) is pretty odd, but
what do I know? People (and tools) sometimes write pretty crazy stuff.
AFAICS to fix this, reconsider_full_join_clause() needs to ask if both
sides of the clause (or operands for COALESCE) preserve NULLs. The patch
adds that check before the loop, same place as [1]. The principle is the
same - unsafe expression -> return false -> keep the original clause.
The trouble is, I don't think we have a way to check this property yet.
There are places doing something similar, but nothing that'd check if an
expression is null-preserving (recursively).
After a fair amount of trial and error I came up with a new helper
is_full_join_expr_null_preserving doing this. It pretty much descends
into CoalesceExpr, and checks if the arguments are NULL-preserving.
Still, once we get a non-CoalesceExpr expression, we need to know if
it's null-preserving. The conservative way to do that is
return !contain_nonstrict_functions(node) &&
contain_vars_of_level(node, 0);
Which means it may contain only strict functions (NULL -> NULL) and some
vars. This rules out constants, functions that discard NULLs, etc. It
also rejects strict-behaving functions that are not labeled as such, but
I don't know how to do better.
There's also a special case for PlaceHolderVar, to preserve the
optimization for expressions calculated below the join (as the result
will be nulled no matter the expression).
Maybe we could improve this further, but this query pattern is pretty
strange, so it should be enough to not return incorrect results. I don't
think it's worth more effort.
The patch is pretty small. I've spent a bit of time with sqlfiddle to
check we get the same behavior as other databases (MSSQL and Oracle).
And that we produce incorrect results without the fix.
My questions are pretty much:
1) Is is_full_join_expr_null_preserving() necessary? Or is there some
existing piece of code checking the expression preserves NULLs? Possibly
with some additional smartness I haven't thought of?
2) Is is_full_join_expr_null_preserving() correct?
regards
[1] https://postgr.es/m/[email protected]
--
Tomas Vondra
From 50197ae78b0333c1a90e1e4c3401e5847878ead2 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Sun, 8 Nov 2026 16:35:55 +0100
Subject: [PATCH v1-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, while retaining support for nested COALESCE expressions
and PlaceHolderVars. Add regression coverage for unsafe operands and
safe nested FULL JOIN USING cases.
---
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 598b62b6764..874a7d44735 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);
@@ -2456,6 +2457,15 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
left_relids = rinfo->left_relids;
right_relids = rinfo->right_relids;
+ /*
+ * 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);
@@ -2611,6 +2621,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 7773852e61b..b41f2157490 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 handling of join aliases when flattening multiple levels of subquery
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index a49d343ec32..eb7135efe8b 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 handling of join aliases when flattening multiple levels of subquery
--
--
2.55.0
