Use the join collation when unique-ifying a semijoin's RHS A semijoin whose RHS is unique-ified groups the RHS on the expressions in SpecialJoinInfo.semi_rhs_exprs. Those were recorded with whatever collation the RHS expression itself exposes, which need not be the collation the join compares with. Neither SortGroupClause nor the pathkey machinery carries a collation of its own, so both Unique-over-Sort and HashAggregate then grouped by the wrong equality: values the join considers equal survived, and the following inner join emitted the outer row once per survivor.
With a non-deterministic collation on one side, "SELECT count(*) FROM t WHERE c IN (SELECT c0 FROM t2)" therefore counted more rows than the same predicate reports for the rows of t. Label each RHS expression with the operator's input collation, the same treatment process_equivalence() gives to equivalence class members. Every consumer of semi_rhs_exprs reads the collation off the expression, so this fixes the sort-based and hash-based paths together; in the branches where create_unique_path() also passes these expressions to relation_has_unique_index_for(), it likewise stops a unique index built with a different collation from being taken as proof that unique-ification can be skipped. The same goes for a subquery's DISTINCT computed under a different collation, since translate_sub_tlist() punts on the relabeled expressions. Reported-by: Suyang Zhong <[email protected]> Author: Andrey Rachitskiy <[email protected]> Reviewed-by: Tender Wang <[email protected]> Reviewed-by: Richard Guo <[email protected]> Reviewed-by: Alexander Korotkov <[email protected]> Discussion: https://postgr.es/m/19633-647cd4c73a84b085%40postgresql.org Backpatch-through: 14 Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/db0208bfcdcf499b3179d879e478e8779c00308a Modified Files -------------- src/backend/optimizer/plan/initsplan.c | 11 +++++- src/test/regress/expected/collate.icu.utf8.out | 54 ++++++++++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 27 +++++++++++++ 3 files changed, 91 insertions(+), 1 deletion(-)
