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

[jian he]

On Tue, Oct 6, 2026 at 11:28 AM shihao zhong <zhong950...@gmail.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/

diff --git a/src/backend/optimizer/util/plancat.c 
b/src/backend/optimizer/util/plancat.c
index b73bc574bf7..04d20485713 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1068,12 +1068,12 @@ infer_arbiter_indexes(PlannerInfo *root)
                {
                        int                     attno = 
idxRel->rd_index->indkey.values[natt];
 
-                       if (attno == 0)
+                       if (attno == InvalidAttrNumber)
                        {
                                Node       *expr = (Node *) lfirst(idxExprCell);
 
                                idxExprCell = lnext(idxExprs, idxExprCell);
-                               if (!IsA(expr, Var) || ((Var *) expr)->varattno 
== 0)
+                               if (!IsA(expr, Var) || ((Var *) expr)->varattno 
== InvalidAttrNumber)
                                {
                                        idxOtherExprs = lappend(idxOtherExprs, 
expr);
                                        continue;
@@ -1264,7 +1264,7 @@ infer_collation_opclass_match(InferenceElem *elem, 
Relation idxRel,
                }
 
                /* If one matching index att found, good enough -- return true 
*/
-               if (attno != 0)
+               if (attno != InvalidAttrNumber)
                {
                        if (IsA(elem->expr, Var) &&
                                ((Var *) elem->expr)->varattno == attno)