On 11/12/26 16:40, Tomas Vondra wrote:
> On 11/12/26 16:18, Kirill Reshke wrote:
>> On Thu, 12 Nov 2026 at 17:57, Tomas Vondra <[email protected]> wrote:
>>>
>>> On 11/12/26 11:28, Tomas Vondra wrote:
>>>>
>>>> ...
>>>>
>>>> I suppose an expression walker that does not descend into PlaceHolderVar
>>>> nodes (with phlevelsup = 0) would make this work correctly.
>>>>
>>> Here's v3 doing it that way.
>>>
>>> --
>>> Tomas Vondra
>>
>> v3 looks totally fine for me, perf regressions from my report
>> eliminated - good.
>> There are maybe some more cases to recurce, but we shouldn't defer
>> this bugs fix commit because of this. LGTM
>>
> 
> True. I picked only a subset of nodes for v3, but I suspect it might be
> expanded to the same set of nodes as contain_nonstrict_functions_walker
> and check_functions_in_node. I don't know if all of them can appear in
> the kind of quals we're processing.
> 
> This kind of clauses is not very common, so I was conservative. But it's
> not great to cause a regression either ... I have to think about it.
> 

I kept thinking about this, so I gave the expression walker idea a go,
and I like it. The master/15 patches are attached.

The new walker mostly mimics contain_nonstrict_functions_walker() with
some small tweaks for the interesting nodes (as in the previous patch).
I ended up moving it to clauses.c, for two reasons. It makes it easier
to keep the two walkers in sync when they're right next to each other.
It also means we don't need to export e.g. is_strict_saop (which is now
static in clauses.c).

I like this version more than v3. The patch is only a little bit larger,
and I trust this "copy of existing walker" more than I trust my earlier
limited implementation.

I'm now fairly happy with the patch. I'm considering whether to push it
now before the 19 freeze ...


regards

-- 
Tomas Vondra
From fd64356ca7fdab1368e14168717fd17dbb0c180b Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Fri, 13 Nov 2026 12:34:20 +0100
Subject: [PATCH v4-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).

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 <[email protected]>
Reviewed-by: Ayush Tiwari <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 15
---
 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(+)

diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index b15f6603dd6..d2caeb5e7a3 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2258,6 +2258,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);
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index c38c76beb4d..8360906584c 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -154,6 +154,8 @@ static Node *substitute_actual_srf_parameters_mutator(Node *node,
 													  substitute_actual_srf_parameters_context *context);
 static bool pull_paramids_walker(Node *node, Bitmapset **context);
 
+static bool full_join_expr_nonstrict_walker(Node *node, bool *found_nullable);
+
 
 /*****************************************************************************
  *		Aggregate-function clause manipulation
@@ -941,6 +943,8 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
  * one or more Var or Param nodes (as appropriate for the caller's need), and
  * now wishes to prove that the expression result will be NULL if any of these
  * inputs is NULL.  If we return false, then the proof succeeded.
+ *
+ * full_join_expr_nonstrict_walker() mirrors this logic, so keep the two in sync.
  */
 bool
 contain_nonstrict_functions(Node *clause)
@@ -1082,6 +1086,197 @@ contain_nonstrict_functions_walker(Node *node, void *context)
 								  context);
 }
 
+/*
+ * is_full_join_expr_null_preserving
+ *	  Check whether an expression becomes NULL on null-extended input.
+ *
+ * The caller has verified that all referenced rels belong to one input.
+ *
+ * We conservatively require that the expression contains no non-strict
+ * constructs, and at least one level-zero Var or PlaceHolderVar.  The latter
+ * test rules out non-NULL constants and pseudoconstants appearing as COALESCE
+ * arguments.
+ *
+ * A level-zero PlaceHolderVar is evaluated below the join, so its result is
+ * nulled regardless of the contained expression, and we don't look inside
+ * it.  COALESCE is not strict in any one argument, but preserves NULL if all
+ * its arguments do.
+ *
+ * This mirrors contain_nonstrict_functions_walker(), except for the handling
+ * of Vars, PlaceHolderVars and COALESCE, so keep the two in sync.
+  */
+bool
+is_full_join_expr_null_preserving(Node *node)
+{
+	bool		found_nullable = false;
+
+	if (full_join_expr_nonstrict_walker(node, &found_nullable))
+		return false;
+
+	return found_nullable;
+}
+
+/*
+ * Walker for is_full_join_expr_null_preserving.  Returns true if it finds a
+ * construct that might produce non-NULL output for a null-extended input,
+ * and sets *found_nullable if it finds an input nulled by the join.
+ */
+static bool
+full_join_expr_nonstrict_walker(Node *node, bool *found_nullable)
+{
+	if (node == NULL)
+		return false;
+	if (IsA(node, Var))
+	{
+		if (((Var *) node)->varlevelsup == 0)
+			*found_nullable = true;
+		return false;
+	}
+	else if (IsA(node, PlaceHolderVar))
+	{
+		if (((PlaceHolderVar *) node)->phlevelsup == 0)
+		{
+			/* don't descend into the contained expression */
+			*found_nullable = true;
+			return false;
+		}
+		/* else fall through to check the contained expression */
+	}
+	else if (IsA(node, Aggref))
+	{
+		/* an aggregate could return non-null with null input */
+		return true;
+	}
+	else if (IsA(node, GroupingFunc))
+	{
+		/*
+		 * A GroupingFunc doesn't evaluate its arguments, and therefore must
+		 * be treated as nonstrict.
+		 */
+		return true;
+	}
+	else if (IsA(node, WindowFunc))
+	{
+		/* a window function could return non-null with null input */
+		return true;
+	}
+	else if (IsA(node, SubscriptingRef))
+	{
+		SubscriptingRef *sbsref = (SubscriptingRef *) node;
+		const SubscriptRoutines *sbsroutines;
+
+		/* Subscripting assignment is always presumed nonstrict */
+		if (sbsref->refassgnexpr != NULL)
+			return true;
+		/* Otherwise we must look up the subscripting support methods */
+		sbsroutines = getSubscriptingRoutines(sbsref->refcontainertype, NULL);
+		if (!(sbsroutines && sbsroutines->fetch_strict))
+			return true;
+		/* else fall through to check args */
+	}
+	else if (IsA(node, DistinctExpr))
+	{
+		/* IS DISTINCT FROM is inherently non-strict */
+		return true;
+	}
+	else if (IsA(node, NullIfExpr))
+	{
+		/* NULLIF is inherently non-strict */
+		return true;
+	}
+	else if (IsA(node, ScalarArrayOpExpr))
+	{
+		ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
+
+		if (!is_strict_saop(expr, false))
+			return true;
+		/* else fall through to check args */
+	}
+	else if (IsA(node, BoolExpr))
+	{
+		BoolExpr   *expr = (BoolExpr *) node;
+
+		switch (expr->boolop)
+		{
+			case AND_EXPR:
+			case OR_EXPR:
+				/* AND, OR are inherently non-strict */
+				return true;
+			default:
+				break;
+		}
+	}
+	else if (IsA(node, SubLink))
+	{
+		/* In some cases a sublink might be strict, but in general not */
+		return true;
+	}
+	else if (IsA(node, SubPlan))
+		return true;
+	else if (IsA(node, AlternativeSubPlan))
+		return true;
+	else if (IsA(node, FieldStore))
+		return true;
+	else if (IsA(node, CoerceViaIO))
+	{
+		/*
+		 * CoerceViaIO is strict regardless of whether the I/O functions are,
+		 * so we should skip check_functions_in_node() and just fall through
+		 * to check the arguments.
+		 */
+	}
+	else if (IsA(node, ArrayCoerceExpr))
+	{
+		/*
+		 * ArrayCoerceExpr is strict at the array level, regardless of what
+		 * the per-element expression is; so we should ignore elemexpr and
+		 * recurse only into the arg.
+		 */
+		return full_join_expr_nonstrict_walker((Node *) ((ArrayCoerceExpr *) node)->arg,
+											   found_nullable);
+	}
+	else if (IsA(node, CaseExpr))
+		return true;
+	else if (IsA(node, ArrayExpr))
+		return true;
+	else if (IsA(node, RowExpr))
+		return true;
+	else if (IsA(node, RowCompareExpr))
+		return true;
+	else if (IsA(node, CoalesceExpr))
+	{
+		CoalesceExpr *cexpr = (CoalesceExpr *) node;
+		ListCell   *lc;
+
+		/* COALESCE is non-strict, but preserves NULL if all arguments do */
+		foreach(lc, cexpr->args)
+		{
+			if (!is_full_join_expr_null_preserving(lfirst(lc)))
+				return true;
+		}
+		*found_nullable = true;
+		return false;
+	}
+	else if (IsA(node, MinMaxExpr))
+		return true;
+	else if (IsA(node, XmlExpr))
+		return true;
+	else if (IsA(node, NullTest))
+		return true;
+	else if (IsA(node, BooleanTest))
+		return true;
+	else
+	{
+		/* Check other function-containing nodes */
+		if (check_functions_in_node(node, contain_nonstrict_functions_checker,
+									NULL))
+			return true;
+	}
+
+	return expression_tree_walker(node, full_join_expr_nonstrict_walker,
+								  found_nullable);
+}
+
 /*****************************************************************************
  *		Check clauses for Params
  *****************************************************************************/
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 6c5203dc448..39c18f1ae98 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -35,6 +35,7 @@ extern bool contain_subplans(Node *clause);
 extern char max_parallel_hazard(Query *parse);
 extern bool is_parallel_safe(PlannerInfo *root, Node *node);
 extern bool contain_nonstrict_functions(Node *clause);
+extern bool is_full_join_expr_null_preserving(Node *node);
 extern bool contain_exec_param(Node *clause, List *param_ids);
 extern bool contain_leaked_vars(Node *clause);
 
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 7c68e4bd2b6..924774cbe1c 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6653,6 +6653,144 @@ 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)
+
+-- strict expressions of precomputed values remain null-preserving
+explain (costs off)
+select * from (select coalesce(x, 0) as x from (values (null::int), (1)) v(x)) a
+  full join (values (1), (2)) b(x) on a.x + 0 = b.x
+  where coalesce(a.x + 0, b.x) = 0
+  order by a.x, b.x;
+                                          QUERY PLAN                                           
+-----------------------------------------------------------------------------------------------
+ Sort
+   Sort Key: (COALESCE("*VALUES*".column1, 0)), "*VALUES*_1".column1
+   ->  Hash Full Join
+         Hash Cond: (((COALESCE("*VALUES*".column1, 0)) + 0) = "*VALUES*_1".column1)
+         Filter: (COALESCE(((COALESCE("*VALUES*".column1, 0)) + 0), "*VALUES*_1".column1) = 0)
+         ->  Values Scan on "*VALUES*"
+         ->  Hash
+               ->  Values Scan on "*VALUES*_1"
+                     Filter: (column1 = 0)
+(9 rows)
+
+select * from (select coalesce(x, 0) as x from (values (null::int), (1)) v(x)) a
+  full join (values (1), (2)) b(x) on a.x + 0 = b.x
+  where coalesce(a.x + 0, b.x) = 0
+  order by a.x, b.x;
+ x | x 
+---+---
+ 0 |  
+(1 row)
+
+explain (costs off)
+select * from (select coalesce(x, '{0}') as x from (values (null::int[]), ('{1}')) v(x)) a
+  full join (values (1), (2)) b(x) on a.x[1] = b.x
+  where coalesce(a.x[1], b.x) = 0;
+                                             QUERY PLAN                                              
+-----------------------------------------------------------------------------------------------------
+ Hash Full Join
+   Hash Cond: ((COALESCE("*VALUES*".column1, '{0}'::integer[]))[1] = "*VALUES*_1".column1)
+   Filter: (COALESCE((COALESCE("*VALUES*".column1, '{0}'::integer[]))[1], "*VALUES*_1".column1) = 0)
+   ->  Values Scan on "*VALUES*"
+   ->  Hash
+         ->  Values Scan on "*VALUES*_1"
+               Filter: (column1 = 0)
+(7 rows)
+
+select * from (select coalesce(x, '{0}') as x from (values (null::int[]), ('{1}')) v(x)) a
+  full join (values (1), (2)) b(x) on a.x[1] = b.x
+  where coalesce(a.x[1], b.x) = 0;
+  x  | x 
+-----+---
+ {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..490d4eeed34 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2313,6 +2313,64 @@ 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;
+
+-- strict expressions of precomputed values remain null-preserving
+explain (costs off)
+select * from (select coalesce(x, 0) as x from (values (null::int), (1)) v(x)) a
+  full join (values (1), (2)) b(x) on a.x + 0 = b.x
+  where coalesce(a.x + 0, b.x) = 0
+  order by a.x, b.x;
+
+select * from (select coalesce(x, 0) as x from (values (null::int), (1)) v(x)) a
+  full join (values (1), (2)) b(x) on a.x + 0 = b.x
+  where coalesce(a.x + 0, b.x) = 0
+  order by a.x, b.x;
+
+explain (costs off)
+select * from (select coalesce(x, '{0}') as x from (values (null::int[]), ('{1}')) v(x)) a
+  full join (values (1), (2)) b(x) on a.x[1] = b.x
+  where coalesce(a.x[1], b.x) = 0;
+
+select * from (select coalesce(x, '{0}') as x from (values (null::int[]), ('{1}')) v(x)) a
+  full join (values (1), (2)) b(x) on a.x[1] = b.x
+  where coalesce(a.x[1], b.x) = 0;
+
 --
 -- test LATERAL reference propagation down a multi-level inheritance hierarchy
 -- produced for a multi-level partitioned table hierarchy.
-- 
2.56.0

From 3ed3f838b1c1fb77db339182617e8e1d6e3fd0a4 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Wed, 11 Nov 2026 20:47:24 +0100
Subject: [PATCH v4-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).

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 <[email protected]>
Reviewed-by: Ayush Tiwari <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Discussion: https://postgr.es/m/[email protected]
Backpatch-through: 15
---
 src/backend/optimizer/path/equivclass.c |   9 ++
 src/backend/optimizer/util/clauses.c    | 197 ++++++++++++++++++++++++
 src/include/optimizer/clauses.h         |   1 +
 src/test/regress/expected/join.out      | 135 ++++++++++++++++
 src/test/regress/sql/join.sql           |  58 +++++++
 5 files changed, 400 insertions(+)

diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 439cb651c01..248386a6298 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2481,6 +2481,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);
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index bfba2ce0733..5692a60d512 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -191,6 +191,8 @@ static Node *substitute_actual_parameters_in_from_mutator(Node *node,
 														  substitute_actual_parameters_in_from_context *context);
 static bool pull_paramids_walker(Node *node, Bitmapset **context);
 
+static bool full_join_expr_nonstrict_walker(Node *node, bool *found_nullable);
+
 
 /*****************************************************************************
  *		Aggregate-function clause manipulation
@@ -1066,6 +1068,8 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
  * one or more Var or Param nodes (as appropriate for the caller's need), and
  * now wishes to prove that the expression result will be NULL if any of these
  * inputs is NULL.  If we return false, then the proof succeeded.
+ *
+ * full_join_expr_nonstrict_walker() mirrors this logic, so keep the two in sync.
  */
 bool
 contain_nonstrict_functions(Node *clause)
@@ -1209,6 +1213,199 @@ contain_nonstrict_functions_walker(Node *node, void *context)
 								  context);
 }
 
+/*
+ * is_full_join_expr_null_preserving
+ *	  Check whether an expression becomes NULL on null-extended input.
+ *
+ * The caller has verified that all referenced rels belong to one input.
+ *
+ * We conservatively require that the expression contains no non-strict
+ * constructs, and at least one level-zero Var or PlaceHolderVar.  The latter
+ * test rules out non-NULL constants and pseudoconstants appearing as COALESCE
+ * arguments.
+ *
+ * A level-zero PlaceHolderVar is evaluated below the join, so its result is
+ * nulled regardless of the contained expression, and we don't look inside
+ * it.  COALESCE is not strict in any one argument, but preserves NULL if all
+ * its arguments do.
+ *
+ * This mirrors contain_nonstrict_functions_walker(), except for the handling
+ * of Vars, PlaceHolderVars and COALESCE, so keep the two in sync.
+  */
+bool
+is_full_join_expr_null_preserving(Node *node)
+{
+	bool		found_nullable = false;
+
+	if (full_join_expr_nonstrict_walker(node, &found_nullable))
+		return false;
+
+	return found_nullable;
+}
+
+/*
+ * Walker for is_full_join_expr_null_preserving.  Returns true if it finds a
+ * construct that might produce non-NULL output for a null-extended input,
+ * and sets *found_nullable if it finds an input nulled by the join.
+ */
+static bool
+full_join_expr_nonstrict_walker(Node *node, bool *found_nullable)
+{
+	if (node == NULL)
+		return false;
+	if (IsA(node, Var))
+	{
+		if (((Var *) node)->varlevelsup == 0)
+			*found_nullable = true;
+		return false;
+	}
+	else if (IsA(node, PlaceHolderVar))
+	{
+		if (((PlaceHolderVar *) node)->phlevelsup == 0)
+		{
+			/* don't descend into the contained expression */
+			*found_nullable = true;
+			return false;
+		}
+		/* else fall through to check the contained expression */
+	}
+	else if (IsA(node, Aggref))
+	{
+		/* an aggregate could return non-null with null input */
+		return true;
+	}
+	else if (IsA(node, GroupingFunc))
+	{
+		/*
+		 * A GroupingFunc doesn't evaluate its arguments, and therefore must
+		 * be treated as nonstrict.
+		 */
+		return true;
+	}
+	else if (IsA(node, WindowFunc))
+	{
+		/* a window function could return non-null with null input */
+		return true;
+	}
+	else if (IsA(node, SubscriptingRef))
+	{
+		SubscriptingRef *sbsref = (SubscriptingRef *) node;
+		const SubscriptRoutines *sbsroutines;
+
+		/* Subscripting assignment is always presumed nonstrict */
+		if (sbsref->refassgnexpr != NULL)
+			return true;
+		/* Otherwise we must look up the subscripting support methods */
+		sbsroutines = getSubscriptingRoutines(sbsref->refcontainertype, NULL);
+		if (!(sbsroutines && sbsroutines->fetch_strict))
+			return true;
+		/* else fall through to check args */
+	}
+	else if (IsA(node, DistinctExpr))
+	{
+		/* IS DISTINCT FROM is inherently non-strict */
+		return true;
+	}
+	else if (IsA(node, NullIfExpr))
+	{
+		/* NULLIF is inherently non-strict */
+		return true;
+	}
+	else if (IsA(node, ScalarArrayOpExpr))
+	{
+		ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node;
+
+		if (!is_strict_saop(expr, false))
+			return true;
+		/* else fall through to check args */
+	}
+	else if (IsA(node, BoolExpr))
+	{
+		BoolExpr   *expr = (BoolExpr *) node;
+
+		switch (expr->boolop)
+		{
+			case AND_EXPR:
+			case OR_EXPR:
+				/* AND, OR are inherently non-strict */
+				return true;
+			default:
+				break;
+		}
+	}
+	else if (IsA(node, SubLink))
+	{
+		/* In some cases a sublink might be strict, but in general not */
+		return true;
+	}
+	else if (IsA(node, SubPlan))
+		return true;
+	else if (IsA(node, AlternativeSubPlan))
+		return true;
+	else if (IsA(node, FieldStore))
+		return true;
+	else if (IsA(node, CoerceViaIO))
+	{
+		/*
+		 * CoerceViaIO is strict regardless of whether the I/O functions are,
+		 * so we should skip check_functions_in_node() and just fall through
+		 * to check the arguments.
+		 */
+	}
+	else if (IsA(node, ArrayCoerceExpr))
+	{
+		/*
+		 * ArrayCoerceExpr is strict at the array level, regardless of what
+		 * the per-element expression is; so we should ignore elemexpr and
+		 * recurse only into the arg.
+		 */
+		return full_join_expr_nonstrict_walker((Node *) ((ArrayCoerceExpr *) node)->arg,
+											   found_nullable);
+	}
+	else if (IsA(node, CaseExpr))
+		return true;
+	else if (IsA(node, ArrayExpr))
+		return true;
+	else if (IsA(node, RowExpr))
+		return true;
+	else if (IsA(node, RowCompareExpr))
+		return true;
+	else if (IsA(node, CoalesceExpr))
+	{
+		CoalesceExpr *cexpr = (CoalesceExpr *) node;
+		ListCell   *lc;
+
+		/* COALESCE is non-strict, but preserves NULL if all arguments do */
+		foreach(lc, cexpr->args)
+		{
+			if (!is_full_join_expr_null_preserving(lfirst(lc)))
+				return true;
+		}
+		*found_nullable = true;
+		return false;
+	}
+	else if (IsA(node, MinMaxExpr))
+		return true;
+	else if (IsA(node, XmlExpr))
+		return true;
+	else if (IsA(node, NullTest))
+		return true;
+	else if (IsA(node, BooleanTest))
+		return true;
+	else if (IsA(node, JsonConstructorExpr))
+		return true;
+	else
+	{
+		/* Check other function-containing nodes */
+		if (check_functions_in_node(node, contain_nonstrict_functions_checker,
+									NULL))
+			return true;
+	}
+
+	return expression_tree_walker(node, full_join_expr_nonstrict_walker,
+								  found_nullable);
+}
+
 /*****************************************************************************
  *		Check clauses for Params
  *****************************************************************************/
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index e929789762b..b561c03640d 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -45,6 +45,7 @@ extern bool contain_subplans(Node *clause);
 extern char max_parallel_hazard(Query *parse);
 extern bool is_parallel_safe(PlannerInfo *root, Node *node);
 extern bool contain_nonstrict_functions(Node *clause);
+extern bool is_full_join_expr_null_preserving(Node *node);
 extern bool contain_exec_param(Node *clause, List *param_ids);
 extern bool contain_leaked_vars(Node *clause);
 
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 526430a5ea0..440511d21b5 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6556,6 +6556,141 @@ 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)
+
+-- strict expressions of precomputed values remain null-preserving
+explain (costs off)
+select * from (select coalesce(x, 0) as x from (values (null::int), (1)) v(x)) a
+  full join (values (1), (2)) b(x) on a.x + 0 = b.x
+  where coalesce(a.x + 0, b.x) = 0
+  order by a.x, b.x;
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Sort
+   Sort Key: (COALESCE("*VALUES*".column1, 0)), "*VALUES*_1".column1
+   ->  Merge Full Join
+         ->  Values Scan on "*VALUES*"
+               Filter: ((COALESCE(column1, 0) + 0) = 0)
+         ->  Materialize
+               ->  Values Scan on "*VALUES*_1"
+                     Filter: (column1 = 0)
+(8 rows)
+
+select * from (select coalesce(x, 0) as x from (values (null::int), (1)) v(x)) a
+  full join (values (1), (2)) b(x) on a.x + 0 = b.x
+  where coalesce(a.x + 0, b.x) = 0
+  order by a.x, b.x;
+ x | x 
+---+---
+ 0 |  
+(1 row)
+
+explain (costs off)
+select * from (select coalesce(x, '{0}') as x from (values (null::int[]), ('{1}')) v(x)) a
+  full join (values (1), (2)) b(x) on a.x[1] = b.x
+  where coalesce(a.x[1], b.x) = 0;
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ Merge Full Join
+   ->  Values Scan on "*VALUES*"
+         Filter: ((COALESCE(column1, '{0}'::integer[]))[1] = 0)
+   ->  Materialize
+         ->  Values Scan on "*VALUES*_1"
+               Filter: (column1 = 0)
+(6 rows)
+
+select * from (select coalesce(x, '{0}') as x from (values (null::int[]), ('{1}')) v(x)) a
+  full join (values (1), (2)) b(x) on a.x[1] = b.x
+  where coalesce(a.x[1], b.x) = 0;
+  x  | x 
+-----+---
+ {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..45dcd48d2b3 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2278,6 +2278,64 @@ 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;
+
+-- strict expressions of precomputed values remain null-preserving
+explain (costs off)
+select * from (select coalesce(x, 0) as x from (values (null::int), (1)) v(x)) a
+  full join (values (1), (2)) b(x) on a.x + 0 = b.x
+  where coalesce(a.x + 0, b.x) = 0
+  order by a.x, b.x;
+
+select * from (select coalesce(x, 0) as x from (values (null::int), (1)) v(x)) a
+  full join (values (1), (2)) b(x) on a.x + 0 = b.x
+  where coalesce(a.x + 0, b.x) = 0
+  order by a.x, b.x;
+
+explain (costs off)
+select * from (select coalesce(x, '{0}') as x from (values (null::int[]), ('{1}')) v(x)) a
+  full join (values (1), (2)) b(x) on a.x[1] = b.x
+  where coalesce(a.x[1], b.x) = 0;
+
+select * from (select coalesce(x, '{0}') as x from (values (null::int[]), ('{1}')) v(x)) a
+  full join (values (1), (2)) b(x) on a.x[1] = b.x
+  where coalesce(a.x[1], b.x) = 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

Reply via email to