public inbox for [email protected]
help / color / mirror / Atom feedFrom: Richard Guo <[email protected]>
To: [email protected]
Subject: pgsql: Consider collation when proving subquery uniqueness
Date: Tue, 05 May 2026 01:35:22 +0000
Message-ID: <[email protected]> (raw)
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 <[email protected]>
Reviewed-by: Tom Lane <[email protected]>
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(-)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: pgsql: Consider collation when proving subquery uniqueness
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox