Re: Fold NOT IN / <> ALL expressions containing NULL to FALSE

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/