Attached v2-patch after applying v1 error.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
From 2738bc24fc82836ff5ac5c93107e91a1708b3f22 Mon Sep 17 00:00:00 2001
From: Ilia Evdokimov <[email protected]>
Date: Fri, 11 Sep 2026 14:49:22 +0300
Subject: [PATCH v2] Fold NOT IN / <> ALL with NULL array element to false in
qual context
When a ScalarArrayOpExpr with useOr=false (NOT IN or <> ALL) appears in
a qual context and its array contains a NULL element, the expression can
never evaluate to true: with a strict operator, comparing any value to
NULL yields NULL, so the overall result is either false or NULL. In a
qual, both mean the row is excluded, so the expression can be safely
folded to constant false during eval_const_expressions(). This allows
the planner to eliminate the scan entirely rather than performing it and
discarding all rows.
To inform eval_const_expressions() that an expression is used as a
qual, a new entry point eval_const_expressions_qual() is introduced.
It sets a new is_qual flag in eval_const_expressions_context. The flag
is saved into a local variable and immediately reset to false at the
start of eval_const_expressions_mutator(), so it cannot leak into
sub-expressions where false and NULL are not interchangeable (e.g., an
argument to a non-strict function). The folding checks
func_strict(saop->opfuncid) explicitly to confirm the operator is
strict before applying the optimization.
---
src/backend/commands/copy.c | 2 +-
src/backend/optimizer/plan/planner.c | 7 ++-
src/backend/optimizer/plan/subselect.c | 2 +-
src/backend/optimizer/util/clauses.c | 74 +++++++++++++++++++++++
src/backend/optimizer/util/inherit.c | 2 +-
src/include/optimizer/optimizer.h | 1 +
src/test/regress/expected/planner_est.out | 24 ++++----
7 files changed, 96 insertions(+), 16 deletions(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 003b70852bb..68cb535f73f 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -205,7 +205,7 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
}
/* Reduce WHERE clause to standard list-of-AND-terms form */
- whereClause = eval_const_expressions(NULL, whereClause);
+ whereClause = eval_const_expressions_qual(NULL, whereClause);
whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false);
whereClause = (Node *) make_ands_implicit((Expr *) whereClause);
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index c3c158a253d..d1d311dc9e4 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1460,7 +1460,12 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
* with AND directly under AND, nor OR directly under OR.
*/
if (kind != EXPRKIND_RTFUNC)
- expr = eval_const_expressions(root, expr);
+ {
+ if (kind == EXPRKIND_QUAL)
+ expr = eval_const_expressions_qual(root, expr);
+ else
+ expr = eval_const_expressions(root, expr);
+ }
/*
* If it's a qual or havingQual, canonicalize it.
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 2cf5c15a309..3a7812418a9 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1983,7 +1983,7 @@ convert_EXISTS_to_ANY(PlannerInfo *root, Query *subselect,
subroot.type = T_PlannerInfo;
subroot.glob = root->glob;
subroot.parse = subselect;
- whereClause = eval_const_expressions(&subroot, whereClause);
+ whereClause = eval_const_expressions_qual(&subroot, whereClause);
whereClause = (Node *) canonicalize_qual((Expr *) whereClause, false);
whereClause = (Node *) make_ands_implicit((Expr *) whereClause);
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 55cebe4a74b..3805f429d45 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -71,6 +71,7 @@ typedef struct
List *active_fns;
Node *case_val;
bool estimate;
+ bool is_qual; /* true if simplifying a qual expression */
} eval_const_expressions_context;
typedef struct
@@ -2626,6 +2627,34 @@ eval_const_expressions(PlannerInfo *root, Node *node)
context.active_fns = NIL; /* nothing being recursively simplified */
context.case_val = NULL; /* no CASE being examined */
context.estimate = false; /* safe transformations only */
+ context.is_qual = false; /* not a qual expression */
+ return eval_const_expressions_mutator(node, &context);
+}
+
+/*--------------------
+ * eval_const_expressions_qual
+ *
+ * Same as eval_const_expressions, but informs the simplifier that the
+ * expression is used as a qual (i.e., in a context where NULL and false have
+ * the same effect). This enables additional simplifications, such as folding
+ * a NOT IN / <> ALL expression to constant false when the array contains a
+ * NULL element and the operator is strict.
+ *--------------------
+ */
+Node *
+eval_const_expressions_qual(PlannerInfo *root, Node *node)
+{
+ eval_const_expressions_context context;
+
+ if (root)
+ context.boundParams = root->glob->boundParams; /* bound Params */
+ else
+ context.boundParams = NULL;
+ context.root = root; /* for inlined-function dependencies */
+ context.active_fns = NIL; /* nothing being recursively simplified */
+ context.case_val = NULL; /* no CASE being examined */
+ context.estimate = false; /* safe transformations only */
+ context.is_qual = true; /* expression is used as a qual */
return eval_const_expressions_mutator(node, &context);
}
@@ -2768,6 +2797,7 @@ estimate_expression_value(PlannerInfo *root, Node *node)
context.active_fns = NIL; /* nothing being recursively simplified */
context.case_val = NULL; /* no CASE being examined */
context.estimate = true; /* unsafe transformations OK */
+ context.is_qual = false; /* not a qual expression */
return eval_const_expressions_mutator(node, &context);
}
@@ -2806,6 +2836,13 @@ static Node *
eval_const_expressions_mutator(Node *node,
eval_const_expressions_context *context)
{
+ /*
+ * Save and reset is_qual so that recursive calls don't inherit it by
+ * default.
+ */
+ bool this_node_is_qual = context->is_qual;
+
+ context->is_qual = false;
/* since this function recurses, it could be driven to stack overflow */
check_stack_depth();
@@ -3272,6 +3309,43 @@ eval_const_expressions_mutator(Node *node,
/* Make sure we know underlying function */
set_sa_opfuncid(saop);
+ /*
+ * When simplifying a qual expression (!useOr means NOT IN or
+ * <> ALL), check whether the array contains a NULL element.
+ * If the operator is strict, a NULL in the array means the
+ * expression can never be true.
+ */
+ if (this_node_is_qual && !saop->useOr &&
+ func_strict(saop->opfuncid))
+ {
+ Node *arrayarg = lsecond(saop->args);
+
+ if (IsA(arrayarg, Const) &&
+ !((Const *) arrayarg)->constisnull)
+ {
+ /* Constant array: check for NULLs using bitmap */
+ ArrayType *arrayval =
+ DatumGetArrayTypeP(((Const *) arrayarg)->constvalue);
+
+ if (array_contains_nulls(arrayval))
+ return makeBoolConst(false, false);
+ }
+ else if (IsA(arrayarg, ArrayExpr))
+ {
+ /* Non-const array: check each element */
+ ListCell *lc2;
+
+ foreach(lc2, ((ArrayExpr *) arrayarg)->elements)
+ {
+ Node *elem = (Node *) lfirst(lc2);
+
+ if (IsA(elem, Const) &&
+ ((Const *) elem)->constisnull)
+ return makeBoolConst(false, false);
+ }
+ }
+ }
+
/*
* If all arguments are Consts, and it's a safe function, we
* can fold to a constant
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c
index 6e1d2b14bc4..38074365a5c 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -863,7 +863,7 @@ apply_child_basequals(PlannerInfo *root, RelOptInfo *parentrel,
childqual = adjust_appendrel_attrs(root,
(Node *) rinfo->clause,
1, &appinfo);
- childqual = eval_const_expressions(root, childqual);
+ childqual = eval_const_expressions_qual(root, childqual);
/* check for flat-out constant */
if (childqual && IsA(childqual, Const))
{
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index cb6241e2bdd..5be86a38a2f 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -145,6 +145,7 @@ extern bool contain_volatile_functions_after_planning(Expr *expr);
extern bool contain_volatile_functions_not_nextval(Node *clause);
extern Node *eval_const_expressions(PlannerInfo *root, Node *node);
+extern Node *eval_const_expressions_qual(PlannerInfo *root, Node *node);
extern void convert_saop_to_hashed_saop(Node *node);
diff --git a/src/test/regress/expected/planner_est.out b/src/test/regress/expected/planner_est.out
index 236cb274a78..806970eebac 100644
--- a/src/test/regress/expected/planner_est.out
+++ b/src/test/regress/expected/planner_est.out
@@ -192,23 +192,23 @@ false, true, false, true);
SELECT explain_mask_costs($$
SELECT * FROM tenk1 WHERE unique1 <> ALL (ARRAY[1, 2, 99, NULL]);$$,
false, true, false, true);
- explain_mask_costs
----------------------------------------------------------
- Seq Scan on tenk1 (cost=N..N rows=1 width=N)
- Filter: (unique1 <> ALL ('{1,2,99,NULL}'::integer[]))
-(2 rows)
+ explain_mask_costs
+------------------------------------
+ Result (cost=N..N rows=0 width=N)
+ Replaces: Scan on tenk1
+ One-Time Filter: false
+(3 rows)
-- Try a non-const array containing a NULL
SELECT explain_mask_costs($$
SELECT * FROM tenk1 WHERE unique1 <> ALL (ARRAY[1, 2, 98, (SELECT 99), NULL]);$$,
false, true, false, true);
- explain_mask_costs
--------------------------------------------------------------------------------------
- Seq Scan on tenk1 (cost=N..N rows=1 width=N)
- Filter: (unique1 <> ALL (ARRAY[1, 2, 98, (InitPlan expr_1).col1, NULL::integer]))
- InitPlan expr_1
- -> Result (cost=N..N rows=1 width=N)
-(4 rows)
+ explain_mask_costs
+------------------------------------
+ Result (cost=N..N rows=0 width=N)
+ Replaces: Scan on tenk1
+ One-Time Filter: false
+(3 rows)
-- Verify that scalarineqsel() works on "char" columns
CREATE TEMP TABLE char_table_1 AS
--
2.34.1
