From 41a7e174ed2c396af9fdb327d112773ac796acfb Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Wed, 9 Sep 2026 13:35:29 +0300 Subject: [PATCH v3] 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. Reported-by: Suyang Zhong Author: Andrey Rachitskiy Reviewed-by: Tender Wang Reviewed-by: Alexander Korotkov Discussion: https://postgr.es/m/19633-647cd4c73a84b085%40postgresql.org Backpatch-through: 14 --- src/backend/optimizer/plan/initsplan.c | 13 ++- .../regress/expected/collate.icu.utf8.out | 98 +++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 43 ++++++++ 3 files changed, 153 insertions(+), 1 deletion(-) diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index fb6f81453ea..7e519e9b4a7 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -2524,7 +2524,18 @@ compute_semijoin_info(PlannerInfo *root, SpecialJoinInfo *sjinfo, List *clause) /* so far so good, keep building lists */ semi_operators = lappend_oid(semi_operators, opno); - semi_rhs_exprs = lappend(semi_rhs_exprs, copyObject(right_expr)); + + /* + * Ensure that the RHS expression exposes the join operator's input + * collation. Unique-ification groups on this expression and takes the + * collation from it, since SortGroupClause carries none, so leaving + * the expression's own collation here would group by different + * equality semantics than the semijoin compares with. + */ + semi_rhs_exprs = lappend(semi_rhs_exprs, + canonicalize_ec_expression((Expr *) copyObject(right_expr), + exprType(right_expr), + op->inputcollid)); } /* Punt if we didn't find at least one column to unique-ify */ diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index eb483b9015e..9cc1b5e7726 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -1988,6 +1988,104 @@ ORDER BY 1; ghi (4 rows) +-- Unique-ification of an IN/semijoin RHS must group under the join's collation +-- rather than the one exposed by the RHS expression. Otherwise values the join +-- considers equal survive unique-ification and duplicate the outer rows. +CREATE TABLE t_semi_ci (c1 text COLLATE case_insensitive); +CREATE TABLE t_semi_cs (c0 text); +INSERT INTO t_semi_ci VALUES ('a'), ('x'), ('y'); +INSERT INTO t_semi_cs VALUES ('a'), ('a'); +ANALYZE t_semi_ci, t_semi_cs; +-- 'A' collides with 'a' only under case_insensitive. Add it after ANALYZE, so +-- that the statistics keep making unique-ification look worthwhile and the +-- plans below stay the ones we mean to test. +INSERT INTO t_semi_cs VALUES ('A'); +-- The predicate holds for exactly one row, so filtering on it must return one. +SELECT c1, c1 IN (SELECT c0 FROM t_semi_cs) AS p FROM t_semi_ci ORDER BY c1; + c1 | p +----+--- + a | t + x | f + y | f +(3 rows) + +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); + count +------- + 1 +(1 row) + +-- Both unique-ification strategies must use the join collation. +SET enable_hashagg TO off; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); + QUERY PLAN +--------------------------------------------------------------------- + Aggregate + -> Nested Loop + Join Filter: (t_semi_ci.c1 = ((t_semi_cs.c0)::text)) + -> Unique + -> Sort + Sort Key: t_semi_cs.c0 COLLATE case_insensitive + -> Seq Scan on t_semi_cs + -> Seq Scan on t_semi_ci +(8 rows) + +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); + count +------- + 1 +(1 row) + +RESET enable_hashagg; +SET enable_sort TO off; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); + QUERY PLAN +------------------------------------------------------------ + Aggregate + -> Nested Loop + Join Filter: (t_semi_ci.c1 = (t_semi_cs.c0)::text) + -> HashAggregate + Group Key: (t_semi_cs.c0)::text + -> Seq Scan on t_semi_cs + -> Seq Scan on t_semi_ci +(7 rows) + +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); + count +------- + 1 +(1 row) + +RESET enable_sort; +-- A unique index on the RHS proves uniqueness under its own collation only, so +-- it must not be taken as a reason to skip unique-ification for a join that +-- compares under a different one. The filler rows are what make unique-ifying +-- the RHS look worthwhile, and so put that decision on the table at all; the +-- plan is checked too, so that a costing change cannot quietly turn this into +-- a test of nothing. +CREATE TABLE t_semi_uq (c0 text UNIQUE); +INSERT INTO t_semi_uq VALUES ('a'), ('A'), ('v1'), ('v2'); +ANALYZE t_semi_uq; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_uq); + QUERY PLAN +---------------------------------------------------------- + Aggregate + -> Hash Right Semi Join + Hash Cond: ((t_semi_uq.c0)::text = t_semi_ci.c1) + -> Seq Scan on t_semi_uq + -> Hash + -> Seq Scan on t_semi_ci +(6 rows) + +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_uq); + count +------- + 1 +(1 row) + CREATE TABLE test1ci (x text COLLATE case_insensitive); CREATE TABLE test2ci (x text COLLATE case_insensitive); CREATE TABLE test3ci (x text COLLATE case_insensitive); diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index df57ebc8bc8..6e793f9bb1e 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -721,6 +721,49 @@ SELECT * FROM test3cs t1 WHERE t1.x = t2.x COLLATE case_insensitive) ORDER BY 1; +-- Unique-ification of an IN/semijoin RHS must group under the join's collation +-- rather than the one exposed by the RHS expression. Otherwise values the join +-- considers equal survive unique-ification and duplicate the outer rows. +CREATE TABLE t_semi_ci (c1 text COLLATE case_insensitive); +CREATE TABLE t_semi_cs (c0 text); +INSERT INTO t_semi_ci VALUES ('a'), ('x'), ('y'); +INSERT INTO t_semi_cs VALUES ('a'), ('a'); +ANALYZE t_semi_ci, t_semi_cs; +-- 'A' collides with 'a' only under case_insensitive. Add it after ANALYZE, so +-- that the statistics keep making unique-ification look worthwhile and the +-- plans below stay the ones we mean to test. +INSERT INTO t_semi_cs VALUES ('A'); + +-- The predicate holds for exactly one row, so filtering on it must return one. +SELECT c1, c1 IN (SELECT c0 FROM t_semi_cs) AS p FROM t_semi_ci ORDER BY c1; +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); + +-- Both unique-ification strategies must use the join collation. +SET enable_hashagg TO off; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); +RESET enable_hashagg; + +SET enable_sort TO off; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs); +RESET enable_sort; + +-- A unique index on the RHS proves uniqueness under its own collation only, so +-- it must not be taken as a reason to skip unique-ification for a join that +-- compares under a different one. The filler rows are what make unique-ifying +-- the RHS look worthwhile, and so put that decision on the table at all; the +-- plan is checked too, so that a costing change cannot quietly turn this into +-- a test of nothing. +CREATE TABLE t_semi_uq (c0 text UNIQUE); +INSERT INTO t_semi_uq VALUES ('a'), ('A'), ('v1'), ('v2'); +ANALYZE t_semi_uq; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_uq); +SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_uq); + CREATE TABLE test1ci (x text COLLATE case_insensitive); CREATE TABLE test2ci (x text COLLATE case_insensitive); CREATE TABLE test3ci (x text COLLATE case_insensitive); -- 2.55.0