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

Hi Ilia, Denis, folks

I tested v5 on master (3c5d9d9). The partition key / dump / upgrade
problem from my last mail is fixed. In addition to Denis's two cases,
I found three more regressions. Each shows master vs v5.

1. Extended statistics are no longer used

create table es (a int, b int, c int);
insert into es select i % 100, i % 7, i % 100
from generate_series(1, 10000) i;
create statistics es_s (mcv)
on (case when b not in (42, null) then 1 else a end), c from es;
analyze es;
explain analyze select * from es
where (case when b not in (42, null) then 1 else a end) = 1
and c = 1;

master: Seq Scan on es (rows=100) (actual rows=100)
v5: Seq Scan on es (rows=1) (actual rows=100)
Filter: ((a = 1) AND (c = 1))

get_relation_statistics() simplifies the stats expression with root,
so it becomes the bare column "a". The clause "a = 1" is then matched
against the attnums in the stats object, not against its
expressions, so the MCV list is ignored. With "else a + 0" instead
of "else a" the estimate stays at 100.

2. Partitionwise aggregate is lost

create table pw (a int, b int) partition by list
((case when b not in (42, null) then 0 else a end));
create table pw0 partition of pw for values in (0);
create table pw1 partition of pw for values in (1);
insert into pw select i % 2, i from generate_series(1, 1000) i;
analyze pw;
set enable_partitionwise_aggregate = on;
explain (costs off)
select (case when b not in (42, null) then 0 else a end), count(*)
from pw group by 1;

master: Append -> HashAggregate per partition (full partitionwise)
v5: Finalize GroupAggregate -> Sort -> Append
-> Partial HashAggregate per partition

3. Constraint exclusion on a partition no longer works

set constraint_exclusion = on;
explain (costs off) select * from pw1
where (case when b not in (42, null) then 0 else a end) = 0;

master: Result, One-Time Filter: false (pw1 excluded)
v5: Seq Scan on pw1, Filter: (a = 0)

Issue 2 uses rel->partexprs, like pruning does, so it gets fixed only
if the fix goes where rel->partexprs is built
(set_baserel_partition_key_exprs()), not into partprune.c.
Issue 3 uses rel->partition_qual instead. That is built separately,
by the expression_planner() call in set_baserel_partition_constraint(),
without root, so it needs a fix of its own.

Regards,
--
Rustam Allakov