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
