v3-0002-Teach-expr_is_nonnullable-to-handle-CoalesceExprs.patch

From cdc758281d53dd58d65fb727547762ed9a2744ab Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Tue, 25 Nov 2025 17:33:48 +0900
Subject: [PATCH v3 2/3] Teach expr_is_nonnullable() to handle CoalesceExprs

Currently, the function expr_is_nonnullable() checks only Const and
Var expressions to determine if an expression is non-nullable.  This
patch extends the detection logic to handle CoalesceExprs.

A CoalesceExpr returns NULL if and only if all its arguments are NULL.
Therefore, we can determine that a CoalesceExpr cannot be NULL if at
least one of its arguments can be proven non-nullable.

This can enable several downstream optimizations, such as reducing a
"COALESCE(...) IS [NOT] NULL" qual to constant true or false, and
converting "COUNT(COALESCE(...))" to the more efficient "COUNT(*)".
---
 src/backend/optimizer/util/clauses.c    | 56 +++++++++++++++----------
 src/test/regress/expected/predicate.out | 39 +++++++++++++++++
 src/test/regress/sql/predicate.sql      | 20 +++++++++
 3 files changed, 93 insertions(+), 22 deletions(-)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 2583cd66509..933ac38d62e 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3572,30 +3572,27 @@ eval_const_expressions_mutator(Node *node,
 
 					return makeBoolConst(result, false);
 				}
-				if (!ntest->argisrow && arg && IsA(arg, Var) && context->root)
+				if (!ntest->argisrow && arg &&
+					expr_is_nonnullable(context->root, (Expr *) arg, false))
 				{
-					Var		   *varg = (Var *) arg;
 					bool		result;
 
-					if (var_is_nonnullable(context->root, varg, false))
+					switch (ntest->nulltesttype)
 					{
-						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);
+						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);
@@ -4330,8 +4327,8 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
  * nullability information before RelOptInfos are generated.  These should
  * pass 'use_rel_info' as false.
  *
- * For now, we only support Var and Const.  Support for other node types may
- * be possible.
+ * For now, we only support Var, Const, and CoalesceExpr.  Support for other
+ * node types may be possible.
  */
 bool
 expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
@@ -4339,7 +4336,22 @@ expr_is_nonnullable(PlannerInfo *root, Expr *expr, bool use_rel_info)
 	if (IsA(expr, Var) && root)
 		return var_is_nonnullable(root, (Var *) expr, use_rel_info);
 	if (IsA(expr, Const))
-		return !castNode(Const, expr)->constisnull;
+		return !((Const *) expr)->constisnull;
+	if (IsA(expr, CoalesceExpr))
+	{
+		/*
+		 * A CoalesceExpr returns NULL if and only if all its arguments are
+		 * NULL.  Therefore, we can determine that a CoalesceExpr cannot be
+		 * NULL if at least one of its arguments can be proven non-nullable.
+		 */
+		CoalesceExpr *coalesceexpr = (CoalesceExpr *) expr;
+
+		foreach_ptr(Expr, arg, coalesceexpr->args)
+		{
+			if (expr_is_nonnullable(root, arg, use_rel_info))
+				return true;
+		}
+	}
 
 	return false;
 }
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)