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

Hi Ilia,

I checked v5 and found two regressions.

1. ON CONFLICT no longer matches an expression index.

create temp table t (a int, b int);
create unique index ti on t
((case when b not in (42, null) then 1 else a end));

insert into t values (1, 1)
on conflict ((case when b not in (42, null) then 1 else a end))
do nothing;

master: INSERT 0 1
v5: ERROR: there is no unique or exclusion constraint matching
the ON CONFLICT specification

The CASE becomes a plain column in ON CONFLICT, while the index
is still classified as an expression index.

2. Partition pruning stops working.

create temp table p (a int, b int) partition by list
((case when a not in (42, null) then 1 else b end));
create temp table p1 partition of p for values in (1);
create temp table p2 partition of p for values in (2);

explain (costs off)
select * from p
where (case when a not in (42, null) then 1 else b end) = 1;

master scans only p1. v5 scans both p1 and p2.

The query condition folds to b = 1, but the partition key keeps
the CASE because it is simplified with root == NULL. The
expressions no longer match.

Best regards,
Denis Smirnov