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

On Thu, Oct 8, 2026 at 12:41 AM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
> On Mon, Oct 5, 2026 at 11:28 PM shihao zhong <zhong950419(at)gmail(dot)com> wrote:
> > 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.

> Seems like there are a number of issues in this thread but so far I
> see only one open item for the whole thing. Should there be more than
> one open item?

I've pushed fixes for #1, #3 and #4, after makeing cosmetic tweaks.

Regarding #1, nothing more to add.

Regarding #2, this is a bug in v17 and later, not a v19 regression, so
I don't think it should be a v19 open item. And it's in the MERGE
code, so I think Dean is the right person to look at it, not me.

Regarding #3, it is not limited to COALESCE. CASE and NULLIF have the
same problem when they are reduced to one of their inputs, and those
date back to older branches. Even for COALESCE, the first commit that
has this problem is 931766aae, not the NOT NULL-based simplification.
The fix covers all three, but I only back-patched it to v19, since
there are no field reports and it changes the typmod exposed by a
reduced CASE expression.

Regarding #4, the fix is to not trust NOT NULL on a virtual generated
column of a partitioned table. But I don't like this fix at all. I
think the real problem is that a partition is allowed to have a
different virtual generation expression than its parent. The
constraint is enforced with the partition's expression, while a query
on the parent computes the column with the parent's expression. So
even on v18, a NOT NULL column can read as NULL through the parent,
and the same goes for CHECK constraints. That seems fragile to me,
and I suspect it will bite us again. CC'ing Peter here.

Regarding #5, it is not specific to v19 either. You get the same
wrong result with CASE WHEN true THEN a END in place of COALESCE(a, 0),
which has nothing to do with the new simplification. So I don't think
it should be a v19 open item.

- Richard