On 9/26/26 08:13, Denis Smirnov wrote:
create table t(a int);
insert into t values (1), (42), (null);
explain (costs off)
select * from t where (a not in (42, null)) is true;
explain (costs off)
select * from t where (a not in (42, null)) is not true;
Both plans still contain the array comparison. The first condition
could be folded to false, and the second to true.
Fixed. Both treat NULL the same as FALSE, so their argument now gets the
same simplification as a qual:
(a NOT IN (42, NULL)) IS TRUE folds to false, and IS NOT TRUE folds to
true. This also works when the SAOP sits under AND/OR inside the
argument. IS FALSE and the other tests are left alone, becuase their
result depends on the row.
A null array is another case:
explain (costs off)
select * from t where a <> all (null::int[]);
explain (costs off)
select * from t where a = any (null::int[]);
Both plans still contain the array comparison. These comparisons
always return null, so in a where clause they could be folded
to false.
The comment in saop_never_true() says that ordinary constant folding
handles a null array, but this does not happen when the left argument
is a column.
You're right. The comment is wrong. ExecEvalScalarArrayOp returns NULL
for a NULL array whatever the operator's strictness, and for ANY as well
as ALL. saop_never_true() now treats that case as never true, so both a
<> ALL (NULL::int[]) and a = ANY (NULL::int[]) fold to false in WHERE.
On 9/27/26 02:09, Rustam ALLAKOV wrote:
The CASE WHEN folding in eval_const_expressions_mutator also runs at
DDL time via expression_planner(). Because of that, a partition key
that master accepts is now rejected as a constant:
CREATE TABLE pk (a int, b int) PARTITION BY LIST
((CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END));
master: CREATE TABLE
v4: ERROR: cannot use constant expression as partition key
So a cluster that has such a table can't be moved to v4.
pg_dump from master and restore into v4 fails:
ERROR: cannot use constant expression as partition key
ERROR: relation "public.pk" does not exist
pg_upgrade from master to v4 fails in "Restoring database schemas in
the new cluster":
pg_restore: error: could not execute query: ERROR: cannot use
constant expression as partition key
...
CREATE TABLE "public"."pk" (
"a" integer,
"b" integer
)
PARTITION BY LIST ((
CASE
WHEN ("a" <> ALL (ARRAY[42, NULL::integer])) THEN 1
ELSE 0
END));
Good catch. The CASE WHEN simplification runs inside
eval_const_expressions_mutator(), which is also reached through
expression_planner() when a partition key is checked for being constant.
Master already rejects e.g. CASE WHEN a = NULL THEN 1 ELSE 0 END for the
same reason, but v4 widened that set, which breaks dump/restore and
pg_upgrade. In v5 the extra simplifications of CASE WHEN conditions,
FILTER clauses and IS [NOT] TRUE run only when planning a query (root !=
NULL), the same condition already used for simplify_aggref().
Expressions reduced at DDL time therefore fold exactly as they do on master.
Tests: the new tests cases are in predicate.sql. Two existing unique1 =
ANY(NULL) in create_index.sql would now fold to a One-Time Filter and
stop exercising the btree code for a NULL array key, so it now passes
the NULL array as a parameter of a generic plan. . The partition_prune
test with a = any(null::timestamptz[]) simply folds now.
v5 attached.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
From 10c92c3f78096c80e1890e47322481de47eb44dc Mon Sep 17 00:00:00 2001
From: Evdokimov Ilia <[email protected]>
Date: Mon, 28 Sep 2026 19:18:38 +0500
Subject: [PATCH v5] Fold never-true "x op ALL (array)" to false in qual
context
A ScalarArrayOpExpr "x op ALL (array)" with a strict operator can
never yield TRUE if the array contains a NULL element: the comparison
against that element yields NULL, so the whole expression is FALSE or
NULL. The same holds for a NULL array, whatever the operator and for
ANY as well as ALL, since the executor then returns NULL. NOT IN with
a NULL in the list is the common way to write such an expression.
Where NULL and FALSE have the same effect, such an expression can be
replaced by constant FALSE. That lets the planner produce a
One-Time Filter or skip the scan instead of evaluating the comparison
for every row. Add eval_const_expressions_qual(), which runs
eval_const_expressions() and then a post-pass,
simplify_qual_null_saops(), that performs this replacement. The
post-pass looks through AND and OR, where replacing NULL with FALSE
cannot change whether the result is TRUE, but not through NOT or into
any other kind of expression. It runs as a separate pass because NOTs
pushed down by negate_clause(), simplified boolean equalities such as
"(expr) = true", and inlined SQL functions can produce such a
ScalarArrayOpExpr only after its own simplification is done.
eval_const_expressions_qual() is used for every expression that the
planner preprocesses as a qual (WHERE, JOIN/ON and HAVING clauses,
security quals, WITH CHECK OPTION quals, ON CONFLICT DO UPDATE WHERE
and MERGE WHEN conditions), and also for inheritance child quals, the
WHERE clause of an EXISTS sublink converted to ANY, and
COPY FROM ... WHERE. When planning a query, eval_const_expressions()
also applies the post-pass to CASE WHEN conditions, aggregate and
window function FILTER clauses, and the argument of IS TRUE and
IS NOT TRUE. It does not do so without a PlannerInfo, so expressions
that expression_planner() reduces at DDL time fold exactly as before.
Otherwise a partition key such as
"CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END" would now be
rejected as constant, breaking dump/restore and pg_upgrade.
A multidimensional ARRAY[] is excluded: a NULL sub-array does not add
a NULL element, and ARRAY[NULL::int[]] is an empty array, over which
ALL is TRUE.
ON CONFLICT's arbiterWhere is also excluded, via a new
EXPRKIND_ARBITER_WHERE. It is never evaluated as a qual; it is only
matched against partial index predicates by predicate_implied_by(),
and folding it to a bare Const would make that match fail.
An existing test in create_index that used a constant NULL array now
passes it as a parameter of a generic plan, so that it still exercises
the btree code for a NULL array key. The partition_prune test for
"a = any(null::timestamptz[])" and the planner_est tests for "<> ALL"
with a NULL element now fold to a One-Time Filter.
---
src/backend/commands/copy.c | 2 +-
src/backend/optimizer/plan/planner.c | 32 +-
src/backend/optimizer/plan/subselect.c | 2 +-
src/backend/optimizer/util/clauses.c | 185 ++++++++
src/backend/optimizer/util/inherit.c | 2 +-
src/include/optimizer/optimizer.h | 1 +
src/test/regress/expected/create_index.out | 16 +-
src/test/regress/expected/insert_conflict.out | 18 +
src/test/regress/expected/partition_prune.out | 16 +-
src/test/regress/expected/planner_est.out | 24 +-
src/test/regress/expected/predicate.out | 400 ++++++++++++++++++
src/test/regress/sql/create_index.sql | 12 +-
src/test/regress/sql/insert_conflict.sql | 13 +
src/test/regress/sql/predicate.sql | 137 ++++++
14 files changed, 822 insertions(+), 38 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..3bfae2624f7 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 qual-only constant folding in
+ * eval_const_expressions_qual(), which could 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,
@@ -1438,12 +1450,17 @@ 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.
*/
- if (kind == EXPRKIND_QUAL)
+ if (kind == EXPRKIND_QUAL || kind == EXPRKIND_ARBITER_WHERE)
{
expr = (Node *) canonicalize_qual((Expr *) expr, false);
@@ -1458,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);
}
@@ -1474,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
@@ -1491,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;
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..8e4151e78aa 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -142,6 +142,8 @@ static Node *eval_const_expressions_mutator(Node *node,
static bool contain_non_const_walker(Node *node, void *context);
static bool ece_function_is_safe(Oid funcid,
eval_const_expressions_context *context);
+static bool saop_never_true(ScalarArrayOpExpr *saop);
+static Node *simplify_qual_null_saops(Node *node);
static List *simplify_or_arguments(List *args,
eval_const_expressions_context *context,
bool *haveNull, bool *forceTrue);
@@ -2650,6 +2652,170 @@ eval_const_expressions(PlannerInfo *root, Node *node)
return eval_const_expressions_mutator(node, &context);
}
+/*--------------------
+ * eval_const_expressions_qual
+ *
+ * Same as eval_const_expressions, but for an expression that is used as a
+ * qual, i.e. in a context where a NULL result has the same effect as FALSE.
+ * This allows additional simplifications; see simplify_qual_null_saops.
+ *
+ * The input should be in explicit-AND format: the extra simplification does
+ * not look into the elements of an implicit-AND List.
+ *--------------------
+ */
+Node *
+eval_const_expressions_qual(PlannerInfo *root, Node *node)
+{
+ node = eval_const_expressions(root, node);
+ return simplify_qual_null_saops(node);
+}
+
+/*
+ * saop_never_true
+ * Can this ScalarArrayOpExpr never yield TRUE?
+ *
+ * That is so if the array is a NULL constant, in which case the result is
+ * always NULL, or if it is "x op ALL (array)" with a strict operator and an
+ * array known to contain a NULL element, in which case each comparison
+ * against the NULL element yields NULL, so the result is FALSE or NULL.
+ */
+static bool
+saop_never_true(ScalarArrayOpExpr *saop)
+{
+ Node *arrayarg = lsecond(saop->args);
+
+ /*
+ * If the array is NULL, the result is NULL whatever the operator and the
+ * scalar argument are; see ExecEvalScalarArrayOp. That holds for ANY as
+ * well as ALL, and for a non-strict operator too.
+ */
+ if (IsA(arrayarg, Const) && ((Const *) arrayarg)->constisnull)
+ return true;
+
+ if (saop->useOr)
+ return false;
+
+ set_sa_opfuncid(saop);
+ if (!func_strict(saop->opfuncid))
+ return false;
+
+ if (IsA(arrayarg, Const))
+ {
+ Const *arrayconst = (Const *) arrayarg;
+
+ return array_contains_nulls(DatumGetArrayTypeP(arrayconst->constvalue));
+ }
+ else if (IsA(arrayarg, ArrayExpr))
+ {
+ ArrayExpr *arrayexpr = (ArrayExpr *) arrayarg;
+ ListCell *lc;
+
+ /*
+ * In a multidimensional ARRAY[] the elements are sub-arrays, and a
+ * NULL sub-array doesn't produce a NULL element: e.g.
+ * ARRAY[NULL::int[], NULL::int[]] is an empty array, over which ALL
+ * is TRUE.
+ */
+ if (arrayexpr->multidims)
+ return false;
+
+ foreach(lc, arrayexpr->elements)
+ {
+ Node *elem = (Node *) lfirst(lc);
+
+ if (IsA(elem, Const) && ((Const *) elem)->constisnull)
+ return true;
+ }
+ }
+
+ return false;
+}
+
+/*
+ * simplify_qual_null_saops
+ * Simplify an already const-simplified boolean expression that is used
+ * in a context where NULL has the same effect as FALSE, such as a WHERE
+ * or JOIN qual, a CASE WHEN condition, or an aggregate FILTER clause.
+ *
+ * In such a context "x op ALL (array)" is replaced by constant FALSE if
+ * saop_never_true() says it can never yield TRUE. We also look through
+ * AND and OR, since replacing a NULL argument by FALSE cannot change whether
+ * an AND or OR yields TRUE. An AND with a FALSE (or NULL) argument then
+ * reduces to FALSE, and such arguments are dropped from an OR. We must not
+ * look through NOT, since NOT NULL is NULL but NOT FALSE is TRUE, nor into
+ * the arguments of any other kind of expression.
+ *
+ * This is done as a separate pass over the output of
+ * eval_const_expressions_mutator, rather than while simplifying the
+ * ScalarArrayOpExpr node itself, because NOTs pushed down by negate_clause(),
+ * simplified boolean equalities such as "(expr) = true", and inlined SQL
+ * functions can all produce such a node only after its own simplification
+ * is complete.
+ *
+ * eval_const_expressions_mutator also applies this to CASE WHEN conditions,
+ * FILTER clauses and the argument of IS [NOT] TRUE, but only when planning a
+ * query (root != NULL). Expressions reduced by expression_planner() at DDL
+ * time, such as partition keys, are checked for being constant, and must
+ * fold exactly as before so that existing schemas can still be restored.
+ */
+static Node *
+simplify_qual_null_saops(Node *node)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, ScalarArrayOpExpr))
+ {
+ if (saop_never_true((ScalarArrayOpExpr *) node))
+ return makeBoolConst(false, false);
+ }
+ else if (is_andclause(node) || is_orclause(node))
+ {
+ BoolExpr *expr = (BoolExpr *) node;
+ bool isand = (expr->boolop == AND_EXPR);
+ List *newargs = NIL;
+ bool changed = false;
+ ListCell *lc;
+
+ foreach(lc, expr->args)
+ {
+ Node *arg = (Node *) lfirst(lc);
+ Node *newarg = simplify_qual_null_saops(arg);
+
+ if (newarg != arg)
+ changed = true;
+
+ /* FALSE and NULL are equivalent here */
+ if (IsA(newarg, Const) &&
+ (((Const *) newarg)->constisnull ||
+ !DatumGetBool(((Const *) newarg)->constvalue)))
+ {
+ if (isand)
+ return makeBoolConst(false, false);
+ changed = true;
+ continue; /* drop it from the OR */
+ }
+
+ /* Keep the result flat, as eval_const_expressions does */
+ if ((isand && is_andclause(newarg)) ||
+ (!isand && is_orclause(newarg)))
+ newargs = list_concat(newargs, ((BoolExpr *) newarg)->args);
+ else
+ newargs = lappend(newargs, newarg);
+ }
+
+ if (!changed)
+ return node;
+ if (newargs == NIL)
+ return makeBoolConst(false, false);
+ if (list_length(newargs) == 1)
+ return (Node *) linitial(newargs);
+ return (Node *) makeBoolExpr(expr->boolop, newargs, expr->location);
+ }
+
+ return node;
+}
+
#define MIN_ARRAY_SIZE_FOR_HASHED_SAOP 9
/*--------------------
* convert_saop_to_hashed_saop
@@ -2944,6 +3110,10 @@ eval_const_expressions_mutator(Node *node,
aggfilter = (Expr *)
eval_const_expressions_mutator((Node *) expr->aggfilter,
context);
+ /* NULL and FALSE are equivalent in a FILTER clause */
+ if (context->root != NULL)
+ aggfilter = (Expr *)
+ simplify_qual_null_saops((Node *) aggfilter);
/* And build the replacement WindowFunc node */
newexpr = makeNode(WindowFunc);
@@ -3010,7 +3180,12 @@ eval_const_expressions_mutator(Node *node,
case T_Aggref:
node = ece_generic_processing(node);
if (context->root != NULL)
+ {
+ /* NULL and FALSE are equivalent in a FILTER clause */
+ ((Aggref *) node)->aggfilter = (Expr *)
+ simplify_qual_null_saops((Node *) ((Aggref *) node)->aggfilter);
return simplify_aggref((Aggref *) node, context);
+ }
return node;
case T_OpExpr:
{
@@ -3699,6 +3874,9 @@ eval_const_expressions_mutator(Node *node,
/* Simplify this alternative's test condition */
casecond = eval_const_expressions_mutator((Node *) oldcasewhen->expr,
context);
+ /* NULL and FALSE are equivalent in a WHEN condition */
+ if (context->root != NULL)
+ casecond = simplify_qual_null_saops(casecond);
/*
* If the test condition is constant FALSE (or NULL), then
@@ -4141,6 +4319,13 @@ eval_const_expressions_mutator(Node *node,
arg = eval_const_expressions_mutator((Node *) btest->arg,
context);
+
+ /* NULL and FALSE are equivalent to IS TRUE and IS NOT TRUE */
+ if (context->root != NULL &&
+ (btest->booltesttype == IS_TRUE ||
+ btest->booltesttype == IS_NOT_TRUE))
+ arg = simplify_qual_null_saops(arg);
+
if (arg && IsA(arg, Const))
{
/*
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/create_index.out b/src/test/regress/expected/create_index.out
index 7b2640f0e04..53a860620e8 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2413,19 +2413,25 @@ SELECT unique1 FROM tenk1 WHERE unique1 = ANY('{7, 14, 22}') and unique1 = ANY('
---------
(0 rows)
+-- Use a generic plan, since a constant NULL array is folded away by the planner
+SET plan_cache_mode = force_generic_plan;
+PREPARE null_array_key(int[]) AS
+SELECT unique1 FROM tenk1 WHERE unique1 = ANY($1);
explain (costs off)
-SELECT unique1 FROM tenk1 WHERE unique1 = ANY(NULL);
- QUERY PLAN
--------------------------------------------------
+EXECUTE null_array_key(NULL);
+ QUERY PLAN
+----------------------------------------------
Index Only Scan using tenk1_unique1 on tenk1
- Index Cond: (unique1 = ANY (NULL::integer[]))
+ Index Cond: (unique1 = ANY ($1))
(2 rows)
-SELECT unique1 FROM tenk1 WHERE unique1 = ANY(NULL);
+EXECUTE null_array_key(NULL);
unique1
---------
(0 rows)
+DEALLOCATE null_array_key;
+RESET plan_cache_mode;
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 = ANY('{NULL,NULL,NULL}');
QUERY PLAN
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index b4c9bdb0e55..0258e9465e3 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -476,6 +476,24 @@ ERROR: there is no unique or exclusion constraint matching the ON CONFLICT spec
insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) where fruit like '%berry' do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
drop index partial_key_index;
+-- The inference WHERE clause must not be reduced to constant-FALSE even
+-- though it can never be true, else it no longer matches the index predicate
+create unique index null_saop_key_index on insertconflicttest(key) where fruit <> all (array['Apple', null]);
+-- Succeeds
+explain (costs off)
+insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) do nothing;
+ QUERY PLAN
+-------------------------------------------------
+ Insert on insertconflicttest
+ Conflict Resolution: NOTHING
+ Conflict Arbiter Indexes: null_saop_key_index
+ -> Result
+(4 rows)
+
+insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) do nothing;
+insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) and fruit like '%berry' do nothing;
+drop index null_saop_key_index;
+delete from insertconflicttest where key = 1001;
--
-- Test that wholerow references to ON CONFLICT's EXCLUDED work
--
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 191901210a8..0c6262fcde5 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3626,16 +3626,12 @@ select * from stable_qual_pruning
explain (analyze, costs off, summary off, timing off, buffers off)
select * from stable_qual_pruning
where a = any(null::timestamptz[]);
- QUERY PLAN
------------------------------------------------------------------------------------------
- Append (actual rows=0.00 loops=1)
- -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0.00 loops=1)
- Filter: (a = ANY (NULL::timestamp with time zone[]))
- -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0.00 loops=1)
- Filter: (a = ANY (NULL::timestamp with time zone[]))
- -> Seq Scan on stable_qual_pruning3 stable_qual_pruning_3 (actual rows=0.00 loops=1)
- Filter: (a = ANY (NULL::timestamp with time zone[]))
-(7 rows)
+ QUERY PLAN
+-----------------------------------------
+ Result (actual rows=0.00 loops=1)
+ Replaces: Scan on stable_qual_pruning
+ One-Time Filter: false
+(3 rows)
drop table stable_qual_pruning;
--
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
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index feae77cb840..4a09f352625 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -954,3 +954,403 @@ SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN;
(1 row)
DROP TABLE bool_tab;
+--
+-- Test that "x op ALL (array)" with a strict operator and a NULL array
+-- element is reduced to constant-FALSE where NULL and FALSE are equivalent
+--
+CREATE TABLE null_saop_tab (a int, b int, arr int[]);
+INSERT INTO null_saop_tab VALUES (1, 1, NULL), (42, 2, '{}'), (NULL, 3, NULL);
+ANALYZE null_saop_tab;
+-- Ensure NOT IN and <> ALL are reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL);
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL);
+ a | b | arr
+---+---+-----
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a <> ALL (ARRAY[42, b, NULL]);
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE a <> ALL (ARRAY[42, b, NULL]);
+ a | b | arr
+---+---+-----
+(0 rows)
+
+-- Ensure equivalent forms are reduced to constant-FALSE as well
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE NOT (a IN (42, NULL));
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE NOT (a IN (42, NULL));
+ a | b | arr
+---+---+-----
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE NOT (a = ANY (ARRAY[42, NULL]));
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE NOT (a = ANY (ARRAY[42, NULL]));
+ a | b | arr
+---+---+-----
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE NOT NOT (a NOT IN (42, NULL));
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE NOT NOT (a NOT IN (42, NULL));
+ a | b | arr
+---+---+-----
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) = true;
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) = true;
+ a | b | arr
+---+---+-----
+(0 rows)
+
+CREATE FUNCTION null_saop_func(int) RETURNS bool LANGUAGE sql IMMUTABLE
+AS $$ SELECT $1 NOT IN (42, NULL) $$;
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE null_saop_func(a);
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE null_saop_func(a);
+ a | b | arr
+---+---+-----
+(0 rows)
+
+DROP FUNCTION null_saop_func(int);
+-- Ensure it is reduced under AND and OR
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) AND b = 1;
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) AND b = 1;
+ a | b | arr
+---+---+-----
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) OR b = 1;
+ QUERY PLAN
+---------------------------
+ Seq Scan on null_saop_tab
+ Filter: (b = 1)
+(2 rows)
+
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) OR b = 1;
+ a | b | arr
+---+---+-----
+ 1 | 1 |
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab
+WHERE (b = 1 AND a NOT IN (42, NULL)) OR (b = 2 AND NOT (a IN (7, NULL)));
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab
+WHERE (b = 1 AND a NOT IN (42, NULL)) OR (b = 2 AND NOT (a IN (7, NULL)));
+ a | b | arr
+---+---+-----
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (b = 1 OR a NOT IN (42, NULL)) AND a > 0;
+ QUERY PLAN
+---------------------------------
+ Seq Scan on null_saop_tab
+ Filter: ((a > 0) AND (b = 1))
+(2 rows)
+
+SELECT * FROM null_saop_tab WHERE (b = 1 OR a NOT IN (42, NULL)) AND a > 0;
+ a | b | arr
+---+---+-----
+ 1 | 1 |
+(1 row)
+
+-- Ensure it is reduced in a join clause
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab t1 LEFT JOIN null_saop_tab t2
+ ON t1.a = t2.a AND t2.b NOT IN (42, NULL);
+ QUERY PLAN
+------------------------------------
+ Nested Loop Left Join
+ Join Filter: false
+ -> Seq Scan on null_saop_tab t1
+ -> Result
+ Replaces: Scan on t2
+ One-Time Filter: false
+(6 rows)
+
+SELECT * FROM null_saop_tab t1 LEFT JOIN null_saop_tab t2
+ ON t1.a = t2.a AND t2.b NOT IN (42, NULL)
+ORDER BY t1.b;
+ a | b | arr | a | b | arr
+----+---+-----+---+---+-----
+ 1 | 1 | | | |
+ 42 | 2 | {} | | |
+ | 3 | | | |
+(3 rows)
+
+-- Ensure it is reduced in CASE WHEN conditions and FILTER clauses
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END FROM null_saop_tab;
+ QUERY PLAN
+----------------------------------
+ Seq Scan on public.null_saop_tab
+ Output: b, 0
+(2 rows)
+
+SELECT b, CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END FROM null_saop_tab;
+ b | case
+---+------
+ 1 | 0
+ 2 | 0
+ 3 | 0
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(*) FILTER (WHERE a NOT IN (42, NULL)) FROM null_saop_tab;
+ QUERY PLAN
+-----------------------------------------
+ Aggregate
+ Output: count(*) FILTER (WHERE false)
+ -> Seq Scan on public.null_saop_tab
+ Output: a, b, arr
+(4 rows)
+
+SELECT count(*) FILTER (WHERE a NOT IN (42, NULL)) FROM null_saop_tab;
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, count(*) FILTER (WHERE a NOT IN (42, NULL)) OVER ()
+FROM null_saop_tab;
+ QUERY PLAN
+----------------------------------------------------
+ WindowAgg
+ Output: b, count(*) FILTER (WHERE false) OVER w1
+ Window: w1 AS ()
+ -> Seq Scan on public.null_saop_tab
+ Output: b
+(5 rows)
+
+SELECT b, count(*) FILTER (WHERE a NOT IN (42, NULL)) OVER ()
+FROM null_saop_tab;
+ b | count
+---+-------
+ 1 | 0
+ 2 | 0
+ 3 | 0
+(3 rows)
+
+-- Ensure it is not reduced under NOT
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE NOT (a NOT IN (42, NULL));
+ QUERY PLAN
+----------------------------------------------
+ Seq Scan on null_saop_tab
+ Filter: (a = ANY ('{42,NULL}'::integer[]))
+(2 rows)
+
+SELECT * FROM null_saop_tab WHERE NOT (a NOT IN (42, NULL));
+ a | b | arr
+----+---+-----
+ 42 | 2 | {}
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, CASE WHEN NOT (a NOT IN (42, NULL)) THEN 1 ELSE 0 END
+FROM null_saop_tab;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Seq Scan on public.null_saop_tab
+ Output: b, CASE WHEN (a = ANY ('{42,NULL}'::integer[])) THEN 1 ELSE 0 END
+(2 rows)
+
+SELECT b, CASE WHEN NOT (a NOT IN (42, NULL)) THEN 1 ELSE 0 END
+FROM null_saop_tab;
+ b | case
+---+------
+ 1 | 0
+ 2 | 1
+ 3 | 0
+(3 rows)
+
+-- Ensure it is not reduced where NULL and FALSE are not equivalent
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, a NOT IN (42, NULL) FROM null_saop_tab;
+ QUERY PLAN
+--------------------------------------------------
+ Seq Scan on public.null_saop_tab
+ Output: b, (a <> ALL ('{42,NULL}'::integer[]))
+(2 rows)
+
+SELECT b, a NOT IN (42, NULL) FROM null_saop_tab;
+ b | ?column?
+---+----------
+ 1 |
+ 2 | f
+ 3 |
+(3 rows)
+
+-- Ensure it is not reduced for a multidimensional ARRAY[]: NULL sub-arrays
+-- don't add NULL elements, and here the array is empty, so ALL is true
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE 1 <> ALL (ARRAY[NULL::int[], arr]) ORDER BY b;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: b
+ -> Seq Scan on null_saop_tab
+ Filter: (1 <> ALL (ARRAY[NULL::integer[], arr]))
+(4 rows)
+
+SELECT * FROM null_saop_tab WHERE 1 <> ALL (ARRAY[NULL::int[], arr]) ORDER BY b;
+ a | b | arr
+----+---+-----
+ 1 | 1 |
+ 42 | 2 | {}
+ | 3 |
+(3 rows)
+
+-- Ensure IS TRUE is reduced to constant-FALSE and IS NOT TRUE to constant-TRUE,
+-- while IS FALSE is not reduced
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS TRUE;
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS TRUE;
+ a | b | arr
+---+---+-----
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL) AND b > 0) IS NOT TRUE
+ORDER BY b;
+ QUERY PLAN
+---------------------------------
+ Sort
+ Sort Key: b
+ -> Seq Scan on null_saop_tab
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL) AND b > 0) IS NOT TRUE
+ORDER BY b;
+ a | b | arr
+----+---+-----
+ 1 | 1 |
+ 42 | 2 | {}
+ | 3 |
+(3 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS FALSE;
+ QUERY PLAN
+----------------------------------------------------------
+ Seq Scan on null_saop_tab
+ Filter: ((a <> ALL ('{42,NULL}'::integer[])) IS FALSE)
+(2 rows)
+
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS FALSE;
+ a | b | arr
+----+---+-----
+ 42 | 2 | {}
+(1 row)
+
+-- Ensure a NULL array is reduced to constant-FALSE, for ANY as well as ALL
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a <> ALL (NULL::int[]);
+ QUERY PLAN
+-----------------------------------
+ Result
+ Replaces: Scan on null_saop_tab
+ One-Time Filter: false
+(3 rows)
+
+SELECT * FROM null_saop_tab WHERE a <> ALL (NULL::int[]);
+ a | b | arr
+---+---+-----
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a = ANY (NULL::int[]) OR b = 1;
+ QUERY PLAN
+---------------------------
+ Seq Scan on null_saop_tab
+ Filter: (b = 1)
+(2 rows)
+
+SELECT * FROM null_saop_tab WHERE a = ANY (NULL::int[]) OR b = 1;
+ a | b | arr
+---+---+-----
+ 1 | 1 |
+(1 row)
+
+-- Ensure expressions checked for constness at DDL time are not reduced
+CREATE TABLE null_saop_part (a int)
+ PARTITION BY LIST ((CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END));
+DROP TABLE null_saop_part;
+DROP TABLE null_saop_tab;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 88ca3c80875..4ebb013a8e7 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -921,10 +921,18 @@ SELECT unique1 FROM tenk1 WHERE unique1 = ANY('{7, 14, 22}') and unique1 = ANY('
SELECT unique1 FROM tenk1 WHERE unique1 = ANY('{7, 14, 22}') and unique1 = ANY('{33, 44}'::bigint[]);
+-- Use a generic plan, since a constant NULL array is folded away by the planner
+SET plan_cache_mode = force_generic_plan;
+PREPARE null_array_key(int[]) AS
+SELECT unique1 FROM tenk1 WHERE unique1 = ANY($1);
+
explain (costs off)
-SELECT unique1 FROM tenk1 WHERE unique1 = ANY(NULL);
+EXECUTE null_array_key(NULL);
+
+EXECUTE null_array_key(NULL);
-SELECT unique1 FROM tenk1 WHERE unique1 = ANY(NULL);
+DEALLOCATE null_array_key;
+RESET plan_cache_mode;
explain (costs off)
SELECT unique1 FROM tenk1 WHERE unique1 = ANY('{NULL,NULL,NULL}');
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index d119158549f..ad5fd21781e 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -251,6 +251,19 @@ insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) whe
drop index partial_key_index;
+-- The inference WHERE clause must not be reduced to constant-FALSE even
+-- though it can never be true, else it no longer matches the index predicate
+create unique index null_saop_key_index on insertconflicttest(key) where fruit <> all (array['Apple', null]);
+
+-- Succeeds
+explain (costs off)
+insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) do nothing;
+insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) do nothing;
+insert into insertconflicttest values (1001, 'Raspberry') on conflict (key) where fruit <> all (array['Apple', null]) and fruit like '%berry' do nothing;
+
+drop index null_saop_key_index;
+delete from insertconflicttest where key = 1001;
+
--
-- Test that wholerow references to ON CONFLICT's EXCLUDED work
--
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 0f92bb52435..20e93c524bb 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -448,3 +448,140 @@ SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN;
SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN;
DROP TABLE bool_tab;
+
+--
+-- Test that "x op ALL (array)" with a strict operator and a NULL array
+-- element is reduced to constant-FALSE where NULL and FALSE are equivalent
+--
+CREATE TABLE null_saop_tab (a int, b int, arr int[]);
+INSERT INTO null_saop_tab VALUES (1, 1, NULL), (42, 2, '{}'), (NULL, 3, NULL);
+ANALYZE null_saop_tab;
+
+-- Ensure NOT IN and <> ALL are reduced to constant-FALSE
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL);
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL);
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a <> ALL (ARRAY[42, b, NULL]);
+SELECT * FROM null_saop_tab WHERE a <> ALL (ARRAY[42, b, NULL]);
+
+-- Ensure equivalent forms are reduced to constant-FALSE as well
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE NOT (a IN (42, NULL));
+SELECT * FROM null_saop_tab WHERE NOT (a IN (42, NULL));
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE NOT (a = ANY (ARRAY[42, NULL]));
+SELECT * FROM null_saop_tab WHERE NOT (a = ANY (ARRAY[42, NULL]));
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE NOT NOT (a NOT IN (42, NULL));
+SELECT * FROM null_saop_tab WHERE NOT NOT (a NOT IN (42, NULL));
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) = true;
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) = true;
+
+CREATE FUNCTION null_saop_func(int) RETURNS bool LANGUAGE sql IMMUTABLE
+AS $$ SELECT $1 NOT IN (42, NULL) $$;
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE null_saop_func(a);
+SELECT * FROM null_saop_tab WHERE null_saop_func(a);
+DROP FUNCTION null_saop_func(int);
+
+-- Ensure it is reduced under AND and OR
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) AND b = 1;
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) AND b = 1;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) OR b = 1;
+SELECT * FROM null_saop_tab WHERE a NOT IN (42, NULL) OR b = 1;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab
+WHERE (b = 1 AND a NOT IN (42, NULL)) OR (b = 2 AND NOT (a IN (7, NULL)));
+SELECT * FROM null_saop_tab
+WHERE (b = 1 AND a NOT IN (42, NULL)) OR (b = 2 AND NOT (a IN (7, NULL)));
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (b = 1 OR a NOT IN (42, NULL)) AND a > 0;
+SELECT * FROM null_saop_tab WHERE (b = 1 OR a NOT IN (42, NULL)) AND a > 0;
+
+-- Ensure it is reduced in a join clause
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab t1 LEFT JOIN null_saop_tab t2
+ ON t1.a = t2.a AND t2.b NOT IN (42, NULL);
+SELECT * FROM null_saop_tab t1 LEFT JOIN null_saop_tab t2
+ ON t1.a = t2.a AND t2.b NOT IN (42, NULL)
+ORDER BY t1.b;
+
+-- Ensure it is reduced in CASE WHEN conditions and FILTER clauses
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END FROM null_saop_tab;
+SELECT b, CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END FROM null_saop_tab;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT count(*) FILTER (WHERE a NOT IN (42, NULL)) FROM null_saop_tab;
+SELECT count(*) FILTER (WHERE a NOT IN (42, NULL)) FROM null_saop_tab;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, count(*) FILTER (WHERE a NOT IN (42, NULL)) OVER ()
+FROM null_saop_tab;
+SELECT b, count(*) FILTER (WHERE a NOT IN (42, NULL)) OVER ()
+FROM null_saop_tab;
+
+-- Ensure it is not reduced under NOT
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE NOT (a NOT IN (42, NULL));
+SELECT * FROM null_saop_tab WHERE NOT (a NOT IN (42, NULL));
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, CASE WHEN NOT (a NOT IN (42, NULL)) THEN 1 ELSE 0 END
+FROM null_saop_tab;
+SELECT b, CASE WHEN NOT (a NOT IN (42, NULL)) THEN 1 ELSE 0 END
+FROM null_saop_tab;
+
+-- Ensure it is not reduced where NULL and FALSE are not equivalent
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, a NOT IN (42, NULL) FROM null_saop_tab;
+SELECT b, a NOT IN (42, NULL) FROM null_saop_tab;
+
+-- Ensure it is not reduced for a multidimensional ARRAY[]: NULL sub-arrays
+-- don't add NULL elements, and here the array is empty, so ALL is true
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE 1 <> ALL (ARRAY[NULL::int[], arr]) ORDER BY b;
+SELECT * FROM null_saop_tab WHERE 1 <> ALL (ARRAY[NULL::int[], arr]) ORDER BY b;
+
+-- Ensure IS TRUE is reduced to constant-FALSE and IS NOT TRUE to constant-TRUE,
+-- while IS FALSE is not reduced
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS TRUE;
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS TRUE;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL) AND b > 0) IS NOT TRUE
+ORDER BY b;
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL) AND b > 0) IS NOT TRUE
+ORDER BY b;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS FALSE;
+SELECT * FROM null_saop_tab WHERE (a NOT IN (42, NULL)) IS FALSE;
+
+-- Ensure a NULL array is reduced to constant-FALSE, for ANY as well as ALL
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a <> ALL (NULL::int[]);
+SELECT * FROM null_saop_tab WHERE a <> ALL (NULL::int[]);
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM null_saop_tab WHERE a = ANY (NULL::int[]) OR b = 1;
+SELECT * FROM null_saop_tab WHERE a = ANY (NULL::int[]) OR b = 1;
+
+-- Ensure expressions checked for constness at DDL time are not reduced
+CREATE TABLE null_saop_part (a int)
+ PARTITION BY LIST ((CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END));
+DROP TABLE null_saop_part;
+
+DROP TABLE null_saop_tab;
--
2.43.0
