v1-0002-Reduce-COALESCE-IS-NOT-NULL-quals-during-constant.patch

From bdd4fdc76481fbd66675d45bd13b305bc57d2467 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 25 Nov 2025 17:33:48 +0900
Subject: [PATCH v1 2/2] Reduce "COALESCE IS [NOT] NULL" quals during constant
 folding

The COALESCE expression returns NULL if and only if all its arguments
are NULL.  Therefore, we can determine that a COALESCE expression is
non-nullable by checking if at least one argument is proven non-null.
We can then leverage this information to perform NullTest deduction
for COALESCE expressions during constant folding.
---
 src/backend/optimizer/util/clauses.c    | 45 +++++++++++++++++++++++++
 src/test/regress/expected/predicate.out | 39 +++++++++++++++++++++
 src/test/regress/sql/predicate.sql      | 20 +++++++++++
 3 files changed, 104 insertions(+)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index b85715ab274..769d12a4001 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3591,6 +3591,51 @@ eval_const_expressions_mutator(Node *node,
 						return makeBoolConst(result, false);
 					}
 				}
+				if (!ntest->argisrow && arg && IsA(arg, CoalesceExpr))
+				{
+					CoalesceExpr *coalesceexpr = (CoalesceExpr *) arg;
+					bool		nonnullable = false;
+					bool		result;
+					ListCell   *lc;
+
+					foreach(lc, coalesceexpr->args)
+					{
+						Node	   *coalescearg = (Node *) lfirst(lc);
+
+						if (IsA(coalescearg, Const))
+						{
+							Assert(!((Const *) coalescearg)->constisnull);
+							nonnullable = true;
+							break;
+						}
+						if (IsA(coalescearg, Var) && context->root &&
+							var_is_nonnullable(context->root, (Var *) coalescearg, false))
+						{
+							nonnullable = true;
+							break;
+						}
+					}
+
+					if (nonnullable)
+					{
+						switch (ntest->nulltesttype)
+						{
+							case IS_NULL:
+								result = false;
+								break;
+							case IS_NOT_NULL:
+								result = true;
+								break;
+							default:
+								elog(ERROR, "unrecognized nulltesttype: %d",
+									 (int) ntest->nulltesttype);
+								result = false; /* keep compiler quiet */
+								break;
+						}
+
+						return makeBoolConst(result, false);
+					}
+				}
 
 				newntest = makeNode(NullTest);
 				newntest->arg = (Expr *) arg;
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 66fb0854b88..fc12c0cd106 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -284,6 +284,45 @@ SELECT * FROM pred_tab t1
          ->  Seq Scan on pred_tab t2
 (9 rows)
 
+--
+-- Tests for NullTest reduction for COALESCE expressions
+--
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NOT NULL;
+      QUERY PLAN      
+----------------------
+ Seq Scan on pred_tab
+(1 row)
+
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NOT NULL;
+      QUERY PLAN      
+----------------------
+ Seq Scan on pred_tab
+(1 row)
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on pred_tab
+   One-Time Filter: false
+(3 rows)
+
 DROP TABLE pred_tab;
 -- Validate we handle IS NULL and IS NOT NULL quals correctly with inheritance
 -- parents.
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 32302d60b6d..1fc83e762fc 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -133,6 +133,26 @@ SELECT * FROM pred_tab t1
         (SELECT 1 FROM pred_tab t3, pred_tab t4, pred_tab t5, pred_tab t6
          WHERE t1.a = t3.a AND t6.a IS NULL);
 
+--
+-- Tests for NullTest reduction for COALESCE expressions
+--
+
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NOT NULL;
+
+-- Ensure the IS_NOT_NULL qual is ignored
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NOT NULL;
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, 1) IS NULL;
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab WHERE COALESCE(b, a) IS NULL;
+
 DROP TABLE pred_tab;
 
 -- Validate we handle IS NULL and IS NOT NULL quals correctly with inheritance
-- 
2.39.5 (Apple Git-154)