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

Hi Ilia,

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.

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.

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.

Best regards,
Denis Smirnov