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 <[email protected]> Reviewed-by: Tom Lane <[email protected]> Discussion: https://postgr.es/m/CAMbWs4_XUUSTyzCaRjUeeahWNqi=8zoa5q4coi8zuvedsbk...@mail.gmail.com Backpatch-through: 14 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/5a55ea507a2d9a3fb2d004d64a994ead2ff7cf51 Modified Files -------------- src/backend/optimizer/path/indxpath.c | 11 ++- src/backend/utils/cache/lsyscache.c | 38 ++++++++ src/include/utils/lsyscache.h | 1 + src/test/regress/expected/collate.icu.utf8.out | 123 +++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 45 +++++++++ 5 files changed, 214 insertions(+), 4 deletions(-)
