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

Hi Denis,

Thanks for review.

On 9/23/26 15:07, Denis Smirnov wrote:

> I checked v3. The direct NOT IN form is folded to false, but some
> equivalent forms still scan the table:
>
> create table t(a int);
> insert into t values (1), (42), (null);
>
> -- folded to false
> explain (costs off)
> select * from t where a not in (42, null);
>
> -- these still scan the table
> explain (costs off)
> select * from t where not (a in (42, null));
>
> explain (costs off)
> select * from t where not (a = any (array[42, null]));
>
> explain (costs off)
> select * from t where not not (a not in (42, null));
>
> explain (costs off)
> select * from t where (a not in (42, null)) = true;
>
> create function not_in_null(integer)
> returns boolean
> language sql immutable
> as $$ select $1 not in (42, null) $$;
>
> explain (costs off)
> select * from t where not_in_null(a);
>
> It looks like the expression produced after removing NOT or inlining
> the function does not get another chance to use the new folding.

Yes, that's exactly the cause. The fold was only tried while simplifying
the SAOP node itself, but negate_clause(), simplify_boolean_equality()
and SQL function inlining produce the <> ALL node only after that step.

in new v4 patch the fold is a separate pass, simplify_qual_null_saops(),
run over the already simplified expression. It looks through AND/OR
only: an AND with a FALSE or NULL arguments becomes FALSE, and such
arguments are dropped from an OR. It does not look like through NOT or
into other expressions. This replaces the is_qual flag in
eval_const_expressions_context, so the patch is simpler now. All five of
your examples now produce "One-Time Filter: false".

> There are also CASE WHEN conditions and aggregate FILTER clauses,
> where false and null have the same effect:
>
> explain (costs off, verbose)
> select case when a not in (42, null) then 1 else 0 end
> from t;
>
> explain (costs off, verbose)
> select count(*) filter (where a not in (42, null))
> from t;
>
> explain (costs off, verbose)
> select a, count(*) filter (where a not in (42, null)) over ()
> from t;
>
> These plans still contain the array comparison. The CASE expression
> could become 0, and the filters could become false. This does not
> necessarily mean that the table scan can be removed.

Agreed. The v4-patch the same pass to CASE WHEN conditions and to the
FILTER clauses of aggregates and window functions. Your examples now give:

    Output: 0
    Output: count(*) FILTER (WHERE false)
    Output: a, count(*) FILTER (WHERE false) OVER w1

As you noted, the scan itself is kept in these cases.

> Could you also add regression tests, at least for the issues fixed
> in v3: multidimensional arrays, ON CONFLICT with a partial index,
> and folding under AND/OR?Currently, the patch only updates the
> expected output of two existing queries. Tests checking both results
> and plans would help prevent these issues from coming back.

Done.

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/