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

On Tue, Oct 6, 2026 at 11:28 AM shihao zhong <zhong950419(at)gmail(dot)com> wrote:
>
> 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.
>

/*
* Now that all inference elements were matched, ensure that the
* expression elements from inference clause are not missing any
* cataloged expressions. This does the right thing when unique
* indexes redundantly repeat the same attribute, or if attributes
* redundantly appear multiple times within an inference clause.
*/
if (list_difference(idxOtherExprs, inferElems) != NIL)
continue;

The above comments also need to change?

While at it, we can replace 0 with InvalidAttrNumber, as shown in the
attachment.
Other than that, V1 looks good to me.

--
jian
https://www.enterprisedb.com/