On 9/13/26 21:12, Rustam ALLAKOV wrote:
1. Multidimensional arrays
CREATE TEMP TABLE mda (a int[]);
INSERT INTO mda VALUES (NULL::int[]);
SELECT * FROM mda WHERE 1 <> ALL (ARRAY[NULL::int[], a]);
master: 1 row
v2: 0 rows
Nice catch. I didn't consider multidimensional arrays. The
non-const-array branch in clauses.c walked ArrayExpr->elements and
folded to false whenever it saw a NULL Const there, without checking
multidims. For a multidimensional ArrayExpr each elements entry is a
sub-array, not a scalar - a NULL sub-array doesn't imply a NULL scalar
element of the result.
So we should skip the fold when ((ArrayExpr *) arrayarg)->multidims is true.
2. ON CONFLICT with a partial index
CREATE TEMP TABLE t (a int, b int);
CREATE UNIQUE INDEX ti ON t (a) WHERE b <> ALL (ARRAY[1, NULL]);
INSERT INTO t VALUES (1, 5)
ON CONFLICT (a) WHERE b <> ALL (ARRAY[1, NULL]) DO NOTHING;
master: succeeds
v2: ERROR: there is no unique or exclusion constraint matching
the ON CONFLICT specification
onConflict->arbiterWhere was preprocessed as an ordinary EXPRKIND_QUAL,
so the new folding introduced by this patch reduced it to constant
false. But arbiterWhere is never evaluated at runtime -
infer_arbiter_indexes() only uses it to check, at plan time, whether a
candidate index's predicate is implied by it via predicate_implied_by(),
which has no notion of a bare "false" clause vacuously implying
anything. So the folded arbiterWhere stopped matching the (correctly
unfolded) index predicate, even though the two are logically identical.
Fix: a new preprocess_expression() kind, EXPRKIND_ARBITER_WHERE, that
gets the same qual-shaped treatment as EXPRKIND_QUAL (AND/OR flattening,
canonicalize_qual, make_ands_implicit, etc.) but is routed through plain
eval_const_expressions() instead of eval_const_expressions_qual(), so
it's exempt from the new folding. An alternative would be teaching
predicate_implied_by() that a literal "false" clause vacuously implies
anything - didn't go that route since it's a general-purpose proof
routine used well beyond ON CONFLICT, but open to it if preferred.
I added this fix in v3-0002 patch. If anyone sees a better way to fix
this, happy to hear it.
3. No folding under AND/OR
CREATE TEMP TABLE s (x int);
-- Plans with v2:
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL); -- One-Time Filter: false
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL) AND x = 1; -- Seq Scan
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL) OR false; -- Seq Scan
Perhaps this could be handled in canonicalize_qual().
eval_const_expressions_mutator() clears context->is_qual unconditionally
at entry, so it never reaches past the first node. When the qual is
itself an AND/OR, its arguments - processed via
simplify_and/or_arguments() - never see is_qual = true. Propagating it
into AND/OR arguments is safe, but must not propogate into NOT, CASE, or
non-qual contexts.
simplify_and/or_arguments() now take an is_qual parameter and set
context->is_qual before each of their own recursive calls.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
From c9ad071a4138954cdccd56cd102b1979e4b51795 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Tue, 22 Sep 2026 16:19:36 +0500
Subject: [PATCH v3 1/2] 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 | 106 ++++++++++++++++++++--
src/backend/optimizer/util/inherit.c | 2 +-
src/include/optimizer/optimizer.h | 1 +
src/test/regress/expected/planner_est.out | 24 ++---
7 files changed, 122 insertions(+), 22 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 55a35aa3397..e98afd27efd 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1438,7 +1438,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 5760b616813..f1b95041cee 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1984,7 +1984,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 3e1f210652d..53e189bf013 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
@@ -144,10 +145,12 @@ static bool ece_function_is_safe(Oid funcid,
eval_const_expressions_context *context);
static List *simplify_or_arguments(List *args,
eval_const_expressions_context *context,
- bool *haveNull, bool *forceTrue);
+ bool *haveNull, bool *forceTrue,
+ bool is_qual);
static List *simplify_and_arguments(List *args,
eval_const_expressions_context *context,
- bool *haveNull, bool *forceFalse);
+ bool *haveNull, bool *forceFalse,
+ bool is_qual);
static Node *simplify_boolean_equality(Oid opno, List *args);
static Expr *simplify_function(Oid funcid,
Oid result_type, int32 result_typmod,
@@ -2647,6 +2650,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);
}
@@ -2789,6 +2820,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);
}
@@ -2827,6 +2859,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();
@@ -3293,6 +3332,44 @@ 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) &&
+ !((ArrayExpr *) arrayarg)->multidims)
+ {
+ /* 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
@@ -3317,7 +3394,8 @@ eval_const_expressions_mutator(Node *node,
newargs = simplify_or_arguments(expr->args,
context,
&haveNull,
- &forceTrue);
+ &forceTrue,
+ this_node_is_qual);
if (forceTrue)
return makeBoolConst(true, false);
if (haveNull)
@@ -3345,7 +3423,8 @@ eval_const_expressions_mutator(Node *node,
newargs = simplify_and_arguments(expr->args,
context,
&haveNull,
- &forceFalse);
+ &forceFalse,
+ this_node_is_qual);
if (forceFalse)
return makeBoolConst(false, false);
if (haveNull)
@@ -4411,11 +4490,19 @@ ece_function_is_safe(Oid funcid, eval_const_expressions_context *context)
* The output arguments *haveNull and *forceTrue must be initialized false
* by the caller. They will be set true if a NULL constant or TRUE constant,
* respectively, is detected anywhere in the argument list.
+ *
+ * is_qual should be true if this OR expression is itself being simplified
+ * in a context where FALSE and NULL are interchangeable (see is_qual in
+ * eval_const_expressions_context); it is passed down to each argument's
+ * own eval_const_expressions_mutator() call, since context->is_qual gets
+ * reset to false as a side effect of every such recursive call and so
+ * cannot simply be left set across the whole loop.
*/
static List *
simplify_or_arguments(List *args,
eval_const_expressions_context *context,
- bool *haveNull, bool *forceTrue)
+ bool *haveNull, bool *forceTrue,
+ bool is_qual)
{
List *newargs = NIL;
List *unprocessed_args;
@@ -4451,6 +4538,7 @@ simplify_or_arguments(List *args,
}
/* If it's not an OR, simplify it */
+ context->is_qual = is_qual;
arg = eval_const_expressions_mutator(arg, context);
/*
@@ -4517,11 +4605,16 @@ simplify_or_arguments(List *args,
* The output arguments *haveNull and *forceFalse must be initialized false
* by the caller. They will be set true if a null constant or false constant,
* respectively, is detected anywhere in the argument list.
+ *
+ * is_qual should be true if this AND expression is itself being simplified
+ * in a context where FALSE and NULL are interchangeable; see comments in
+ * simplify_or_arguments.
*/
static List *
simplify_and_arguments(List *args,
eval_const_expressions_context *context,
- bool *haveNull, bool *forceFalse)
+ bool *haveNull, bool *forceFalse,
+ bool is_qual)
{
List *newargs = NIL;
List *unprocessed_args;
@@ -4547,6 +4640,7 @@ simplify_and_arguments(List *args,
}
/* If it's not an AND, simplify it */
+ context->is_qual = is_qual;
arg = eval_const_expressions_mutator(arg, context);
/*
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.43.0
From b388075267c1650a807e1441eb95516ae9cfac85 Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Tue, 22 Sep 2026 17:05:54 +0500
Subject: [PATCH v3 2/2] Don't fold ON CONFLICT's arbiterWhere to constant
false
---
src/backend/optimizer/plan/planner.c | 25 ++++++++++++++++++++-----
1 file changed, 20 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index e98afd27efd..a7cfc23a63b 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -98,6 +98,7 @@ create_upper_paths_hook_type create_upper_paths_hook = NULL;
#define EXPRKIND_TABLEFUNC 11
#define EXPRKIND_TABLEFUNC_LATERAL 12
#define EXPRKIND_GROUPEXPR 13
+#define EXPRKIND_ARBITER_WHERE 14
/*
* Data specific to grouping sets
@@ -1057,10 +1058,21 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name,
preprocess_expression(root,
(Node *) parse->onConflict->arbiterElems,
EXPRKIND_ARBITER_ELEM);
+ /*
+ * arbiterWhere is never evaluated as a runtime qual: it is only
+ * used to match a partial index's predicate via
+ * predicate_implied_by() in infer_arbiter_indexes(). Use
+ * EXPRKIND_ARBITER_WHERE (not EXPRKIND_QUAL) so it still gets the
+ * usual qual-shaped preprocessing (AND/OR flattening,
+ * canonicalize_qual, implicit-AND format) but is not subject to the
+ * is_qual-only constant folding in eval_const_expressions_qual(),
+ * which would replace it with a bare Const and break the
+ * structural predicate match.
+ */
parse->onConflict->arbiterWhere =
preprocess_expression(root,
parse->onConflict->arbiterWhere,
- EXPRKIND_QUAL);
+ EXPRKIND_ARBITER_WHERE);
parse->onConflict->onConflictSet = (List *)
preprocess_expression(root,
(Node *) parse->onConflict->onConflictSet,
@@ -1448,7 +1460,7 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
/*
* If it's a qual or havingQual, canonicalize it.
*/
- if (kind == EXPRKIND_QUAL)
+ if (kind == EXPRKIND_QUAL || kind == EXPRKIND_ARBITER_WHERE)
{
expr = (Node *) canonicalize_qual((Expr *) expr, false);
@@ -1463,7 +1475,8 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
* hashfuncid of any that might execute more quickly by using hash lookups
* instead of a linear search.
*/
- if (kind == EXPRKIND_QUAL || kind == EXPRKIND_TARGET)
+ if (kind == EXPRKIND_QUAL || kind == EXPRKIND_TARGET ||
+ kind == EXPRKIND_ARBITER_WHERE)
{
convert_saop_to_hashed_saop(expr);
}
@@ -1479,7 +1492,9 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
/* Expand SubLinks to SubPlans */
if (root->parse->hasSubLinks)
- expr = SS_process_sublinks(root, expr, (kind == EXPRKIND_QUAL));
+ expr = SS_process_sublinks(root, expr,
+ (kind == EXPRKIND_QUAL ||
+ kind == EXPRKIND_ARBITER_WHERE));
/*
* XXX do not insert anything here unless you have grokked the comments in
@@ -1496,7 +1511,7 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
* would be unable to simplify a top-level AND correctly. Also,
* SS_process_sublinks expects explicit-AND format.)
*/
- if (kind == EXPRKIND_QUAL)
+ if (kind == EXPRKIND_QUAL || kind == EXPRKIND_ARBITER_WHERE)
expr = (Node *) make_ands_implicit((Expr *) expr);
return expr;
--
2.43.0
