[PG19] Wrong results from NOT NULL-based expression simplification

Hi hackers,

I used Opus to analyze the new features in PG19, and it found five
queries that work on 18 and break on 19 and master. Each item has a
script and a patch with its number.

I manually verified these issues and

1. coalesce(b, '') on a NOT NULL column is simplified to plain b, in
the index and in ON CONFLICT. Only the ON CONFLICT side counts it as a
plain column, so they do not match.
create table oc (b text not null);
create unique index on oc (coalesce(b, ''));
insert into oc values ('x') on conflict (coalesce(b, '')) do nothing;
18 inserts the row. 19 gives "there is no unique or exclusion
constraint matching the ON CONFLICT specification". 0001 counts the
index expression as a plain column too.

2. With a join as the MERGE source, only Vars of the join RTE are
marked nullable for NOT MATCHED BY SOURCE, not Vars of the tables
under it.
create table mt (id int);
create table sa (id int not null);
create table sb (id int);
insert into mt values (1);
merge into mt using (sa join sb on sa.id = sb.id) on mt.id = sa.id
when not matched by source then delete
returning sa.id is null;
18 returns t, 19 returns f. 0002 marks them all.

The marking comes from 259a0a99fe3, the fix for bug #18634, which is
also in 17 and 18. It covers a source that is one table, but not a
join. On 18 that does not give wrong values, but planning can fail
with "wrong varnullingrels", the error from that bug. So I think 0002
should be back-patched to 17.

create view mv as select * from mt where exists (select 1 from mt);
merge into mv using (sa join sb on sa.id = sb.id) on mv.id = sa.id
when not matched by source then delete
returning sa.id is null;
wrong varnullingrels (b) (expected (b 6)) for Var 2/1

3. COALESCE on a NOT NULL column is replaced by the column, which can
have another collation.
create collation ci (provider = icu, locale = 'und-u-ks-level2',
deterministic = false);
create table co (t text not null, u text);
insert into co values ('a'), ('A');
select count(*) from
(select coalesce(t, u collate ci) from co group by 1) s;
18 returns 1, 19 returns 2. 0003 adds a RelabelType.

4. NOT IN becomes an anti join when the sub-select's output is NOT
NULL. For a virtual generated column the check runs before the column
is expanded, and a partition can have another expression.
create table p (a int,
v int generated always as (nullif(a, 0)) virtual not null)
partition by list (a);
create table p1 partition of p
(v generated always as (a + 1) virtual) for values in (0);
insert into p values (0);
create table o (id int not null);
insert into o values (1);
select * from o where id not in (select v from p);
18 returns no rows, 19 returns one. 0004 does not trust NOT NULL
there.

5. Two grouping expressions can be simplified to the same Var, and
expressions above the grouping step cannot tell them apart.
create table g (a int not null);
insert into g values (1);
select a, coalesce(a, 0) as ca, coalesce(a, 0) is null as ca_is_null
from g group by grouping sets ((a), (coalesce(a, 0))) order by 1;
ca_is_null is t, f on 18 and f, t on 19. 18 does the same with CASE
WHEN true THEN a END. 0005 wraps the duplicate in a PlaceHolderVar.

Each patch has a test. All five work on 18, so I think they are open
items.

I am less sure about 0005. It is more like a workaround for now. The
grouping nullingrel goes on the Vars inside an expression, not on the
expression, so two equal expressions look the same. Maybe that is
what should change.

I did not try other ways, but I see three. One is to wrap every
grouping expression, so that the mark is on the expression itself.
That looks more correct, but it touches every grouping sets query.
Another is to keep the references to grouping columns as Vars until
setrefs.c, so they are not matched by equal(). That is a bigger
change. The smallest is to stop using NOT NULL to simplify grouping
expressions when there are grouping sets. But then the CASE example
stays wrong.

Thanks,
Shihao