agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Consider collation when proving uniqueness from unique indexes 6+ messages / 1 participants [nested] [flat]
* pgsql: Consider collation when proving uniqueness from unique indexes @ 2026-05-05 01:35 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: Richard Guo @ 2026-05-05 01:35 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 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(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving uniqueness from unique indexes @ 2026-05-05 01:35 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: Richard Guo @ 2026-05-05 01:35 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/b62f514ac5334bc1581d6491dea7ab8482ff745a Modified Files -------------- src/backend/optimizer/path/indxpath.c | 20 ++-- 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, 220 insertions(+), 7 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving uniqueness from unique indexes @ 2026-05-05 01:35 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: Richard Guo @ 2026-05-05 01:35 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/d0e73bb18017f54fb406f0595709ceb4ae3962c9 Modified Files -------------- src/backend/optimizer/path/indxpath.c | 20 ++-- 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, 220 insertions(+), 7 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving uniqueness from unique indexes @ 2026-05-05 01:35 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: Richard Guo @ 2026-05-05 01:35 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/748fe9e6085c998fac693fdf216093657d0104cb Modified Files -------------- src/backend/optimizer/path/indxpath.c | 20 ++-- 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, 220 insertions(+), 7 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving uniqueness from unique indexes @ 2026-05-05 01:35 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: Richard Guo @ 2026-05-05 01:35 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/872c9fae78bc4a98d447940db01a5ca83c461804 Modified Files -------------- src/backend/optimizer/path/indxpath.c | 20 ++-- 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, 220 insertions(+), 7 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving uniqueness from unique indexes @ 2026-05-05 01:35 Richard Guo <rguo@postgresql.org> 0 siblings, 0 replies; 6+ messages in thread From: Richard Guo @ 2026-05-05 01:35 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org 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 Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/8395446dff08a027f11fd0cbbd98150d5e5aa0bb Modified Files -------------- src/backend/optimizer/path/indxpath.c | 20 ++-- 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, 220 insertions(+), 7 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-05-05 01:35 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-05-05 01:35 pgsql: Consider collation when proving uniqueness from unique indexes Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving uniqueness from unique indexes Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving uniqueness from unique indexes Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving uniqueness from unique indexes Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving uniqueness from unique indexes Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving uniqueness from unique indexes Richard Guo <rguo@postgresql.org>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox