postgres-github.git / summary / log / commit / refs

commit    5a55ea507a2d9a3fb2d004d64a994ead2ff7cf51
Author:   Richard Guo <rguo@postgresql.org>
Date:     Tue May 05 01:22:53 2026 +0000

    Consider collation when proving uniqueness from unique indexes
    
    relation_has_unique_index_for() has long had an XXX noting that it
    doesn't check collations when matching a unique index's columns
    against equality clauses.  This was benign as long as all collations
    in play reduced to the same notion of equality, but has been incorrect
    since nondeterministic collations were introduced in PG 12: a unique
    index under a deterministic collation does not prove uniqueness under
    a nondeterministic collation, nor vice versa.
    
    The consequence is wrong query results for any planner optimization
    that consumes the faulty proof, including inner-unique join execution
    (which stops the inner search after the first match per outer row),
    useless-left-join removal, semijoin-to-innerjoin reduction, and
    self-join elimination.
    
    Fix by requiring the index's collation to agree on equality with the
    clause's input collation.  Two collations agree on equality if either
    is InvalidOid (denoting a non-collation-sensitive operation, which
    cannot conflict with the other side), if they have the same OID, or if
    both are deterministic: by definition a deterministic collation treats
    two strings as equal iff they are byte-wise equal (see CREATE
    COLLATION), so any two deterministic collations share the same
    equality relation and the uniqueness proof carries over.  Any mismatch
    involving a nondeterministic collation is rejected.
    
    Back-patch to all supported branches; the bug has existed since
    nondeterministic collations were introduced in PG 12.
    
    Author: Richard Guo <guofenglinux@gmail.com>
    Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
    Discussion: https://postgr.es/m/CAMbWs4_XUUSTyzCaRjUeeahWNqi=8ZOA5Q4coi8zUVEDSBkM6A@mail.gmail.com
    Backpatch-through: 14

[parent: 93da29736649]