agora inbox for pgsql-committers@postgresql.orghelp / color / mirror / Atom feed
pgsql: Consider collation when proving subquery uniqueness 6+ messages / 1 participants [nested] [flat]
* pgsql: Consider collation when proving subquery uniqueness @ 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 subquery uniqueness rel_is_distinct_for()'s RTE_SUBQUERY branch passed only the equality operator from each join clause to query_is_distinct_for(), discarding the operator's input collation. query_is_distinct_for() then verified opfamily compatibility but never checked collations, so a DISTINCT / GROUP BY / set-op operating under one collation was trusted to prove uniqueness for a comparison performed under an unrelated collation. As with the recent fix in relation_has_unique_index_for(), this is unsound for nondeterministic collations and yields wrong query results in any optimization that consumes the proof. Fix by carrying each clause's operator input collation into query_is_distinct_for() and validating it at every check-site against the subquery target expression's collation. Back-patch to all supported branches. query_is_distinct_for() is declared in an installed header, so on stable branches the existing two-list signature is retained as a thin wrapper that forwards to a new collation-aware entry point; external callers continue to receive the historical collation-blind answer. 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/574581b50ac9c63dd9e4abebb731a3b67e5b50f6 Modified Files -------------- src/backend/optimizer/plan/analyzejoins.c | 121 ++++++++++------- src/include/nodes/pathnodes.h | 14 ++ src/include/optimizer/planmain.h | 2 +- src/test/regress/expected/collate.icu.utf8.out | 181 +++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 58 ++++++++ src/tools/pgindent/typedefs.list | 1 + 6 files changed, 324 insertions(+), 53 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving subquery uniqueness @ 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 subquery uniqueness rel_is_distinct_for()'s RTE_SUBQUERY branch passed only the equality operator from each join clause to query_is_distinct_for(), discarding the operator's input collation. query_is_distinct_for() then verified opfamily compatibility but never checked collations, so a DISTINCT / GROUP BY / set-op operating under one collation was trusted to prove uniqueness for a comparison performed under an unrelated collation. As with the recent fix in relation_has_unique_index_for(), this is unsound for nondeterministic collations and yields wrong query results in any optimization that consumes the proof. Fix by carrying each clause's operator input collation into query_is_distinct_for() and validating it at every check-site against the subquery target expression's collation. Back-patch to all supported branches. query_is_distinct_for() is declared in an installed header, so on stable branches the existing two-list signature is retained as a thin wrapper that forwards to a new collation-aware entry point; external callers continue to receive the historical collation-blind answer. 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/bed3ffbf9d952be6c7d739d068cdce44c046dfb7 Modified Files -------------- src/backend/optimizer/plan/analyzejoins.c | 177 ++++++++++++++++-------- src/test/regress/expected/collate.icu.utf8.out | 181 +++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 58 ++++++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 361 insertions(+), 56 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving subquery uniqueness @ 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 subquery uniqueness rel_is_distinct_for()'s RTE_SUBQUERY branch passed only the equality operator from each join clause to query_is_distinct_for(), discarding the operator's input collation. query_is_distinct_for() then verified opfamily compatibility but never checked collations, so a DISTINCT / GROUP BY / set-op operating under one collation was trusted to prove uniqueness for a comparison performed under an unrelated collation. As with the recent fix in relation_has_unique_index_for(), this is unsound for nondeterministic collations and yields wrong query results in any optimization that consumes the proof. Fix by carrying each clause's operator input collation into query_is_distinct_for() and validating it at every check-site against the subquery target expression's collation. Back-patch to all supported branches. query_is_distinct_for() is declared in an installed header, so on stable branches the existing two-list signature is retained as a thin wrapper that forwards to a new collation-aware entry point; external callers continue to receive the historical collation-blind answer. 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/13226050e85d27759c20786504efe9f5994f41ee Modified Files -------------- src/backend/optimizer/plan/analyzejoins.c | 180 ++++++++++++++++-------- src/test/regress/expected/collate.icu.utf8.out | 181 +++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 58 ++++++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 363 insertions(+), 57 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving subquery uniqueness @ 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 subquery uniqueness rel_is_distinct_for()'s RTE_SUBQUERY branch passed only the equality operator from each join clause to query_is_distinct_for(), discarding the operator's input collation. query_is_distinct_for() then verified opfamily compatibility but never checked collations, so a DISTINCT / GROUP BY / set-op operating under one collation was trusted to prove uniqueness for a comparison performed under an unrelated collation. As with the recent fix in relation_has_unique_index_for(), this is unsound for nondeterministic collations and yields wrong query results in any optimization that consumes the proof. Fix by carrying each clause's operator input collation into query_is_distinct_for() and validating it at every check-site against the subquery target expression's collation. Back-patch to all supported branches. query_is_distinct_for() is declared in an installed header, so on stable branches the existing two-list signature is retained as a thin wrapper that forwards to a new collation-aware entry point; external callers continue to receive the historical collation-blind answer. 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/5a24cef082a095aaeadb0b1672d8c1d912335261 Modified Files -------------- src/backend/optimizer/plan/analyzejoins.c | 182 +++++++++++++++++-------- src/test/regress/expected/collate.icu.utf8.out | 181 ++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 58 ++++++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 366 insertions(+), 56 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving subquery uniqueness @ 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 subquery uniqueness rel_is_distinct_for()'s RTE_SUBQUERY branch passed only the equality operator from each join clause to query_is_distinct_for(), discarding the operator's input collation. query_is_distinct_for() then verified opfamily compatibility but never checked collations, so a DISTINCT / GROUP BY / set-op operating under one collation was trusted to prove uniqueness for a comparison performed under an unrelated collation. As with the recent fix in relation_has_unique_index_for(), this is unsound for nondeterministic collations and yields wrong query results in any optimization that consumes the proof. Fix by carrying each clause's operator input collation into query_is_distinct_for() and validating it at every check-site against the subquery target expression's collation. Back-patch to all supported branches. query_is_distinct_for() is declared in an installed header, so on stable branches the existing two-list signature is retained as a thin wrapper that forwards to a new collation-aware entry point; external callers continue to receive the historical collation-blind answer. 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/bab4f7fa56217ff6cbde3b79a5104d2c942b8582 Modified Files -------------- src/backend/optimizer/plan/analyzejoins.c | 182 +++++++++++++++++-------- src/test/regress/expected/collate.icu.utf8.out | 181 ++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 58 ++++++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 366 insertions(+), 56 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Consider collation when proving subquery uniqueness @ 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 subquery uniqueness rel_is_distinct_for()'s RTE_SUBQUERY branch passed only the equality operator from each join clause to query_is_distinct_for(), discarding the operator's input collation. query_is_distinct_for() then verified opfamily compatibility but never checked collations, so a DISTINCT / GROUP BY / set-op operating under one collation was trusted to prove uniqueness for a comparison performed under an unrelated collation. As with the recent fix in relation_has_unique_index_for(), this is unsound for nondeterministic collations and yields wrong query results in any optimization that consumes the proof. Fix by carrying each clause's operator input collation into query_is_distinct_for() and validating it at every check-site against the subquery target expression's collation. Back-patch to all supported branches. query_is_distinct_for() is declared in an installed header, so on stable branches the existing two-list signature is retained as a thin wrapper that forwards to a new collation-aware entry point; external callers continue to receive the historical collation-blind answer. 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/172034f6e08847219239b5fa45f2d4117aadf11b Modified Files -------------- src/backend/optimizer/plan/analyzejoins.c | 182 +++++++++++++++++-------- src/test/regress/expected/collate.icu.utf8.out | 181 ++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 58 ++++++++ src/tools/pgindent/typedefs.list | 1 + 4 files changed, 366 insertions(+), 56 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 subquery uniqueness Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving subquery uniqueness Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving subquery uniqueness Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving subquery uniqueness Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving subquery uniqueness Richard Guo <rguo@postgresql.org> 2026-05-05 01:35 pgsql: Consider collation when proving subquery uniqueness 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