Consider collation when proving subquery uniqueness
Richard Guo <rguo@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
Files
| Path | Change | +/− |
|---|---|---|
| src/backend/optimizer/plan/analyzejoins.c | modified | +69 −52 |
| src/include/nodes/pathnodes.h | modified | +14 −0 |
| src/include/optimizer/planmain.h | modified | +1 −1 |
| src/test/regress/expected/collate.icu.utf8.out | modified | +181 −0 |
| src/test/regress/sql/collate.icu.utf8.sql | modified | +58 −0 |
| src/tools/pgindent/typedefs.list | modified | +1 −0 |
Discussion
- Wrong results from inner-unique joins caused by collation mismatch 5 messages · 2026-04-24 → 2026-05-05