postgres.git / summary / log / commit / refs
commit d237a7a83668bc228f7c58f104ffb25cbb607a1a
Author: Alexander Korotkov <akorotkov@postgresql.org>
Date: Wed Sep 16 09:17:53 2026 +0000
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 <syzhong16@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com>
Discussion: https://postgr.es/m/19633-647cd4c73a84b085%40postgresql.org
Backpatch-through: 14
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(-)
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 8893e37c8f7..199e5d0a580 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2517,9 +2517,18 @@ compute_semijoin_info(PlannerInfo *root, SpecialJoinInfo *sjinfo, List *clause)
if (!(all_btree || all_hash))
return;
+ /*
+ * Ensure the RHS expression exposes the join's input collation (its
+ * type should be OK already); see comments for
+ * canonicalize_ec_expression.
+ */
+ right_expr = (Node *) canonicalize_ec_expression((Expr *) copyObject(right_expr),
+ exprType(right_expr),
+ op->inputcollid);
+
/* so far so good, keep building lists */
semi_operators = lappend_oid(semi_operators, opno);
- semi_rhs_exprs = lappend(semi_rhs_exprs, copyObject(right_expr));
+ semi_rhs_exprs = lappend(semi_rhs_exprs, right_expr);
}
/* 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 cb5795f036e..a32e8965725 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2114,6 +2114,60 @@ SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b');
{A,NULL,C,D,E,F,G,H,I}
(1 row)
+-- Unique-ifying a semijoin's RHS must use the join's collation. test3cs
+-- holds both 'abc' and 'ABC', so test1ci's 'abc' must come out once.
+BEGIN;
+SET LOCAL enable_seqscan TO off;
+SET LOCAL enable_material TO off;
+SET LOCAL enable_hashjoin TO off;
+SET LOCAL enable_mergejoin TO off;
+SET LOCAL enable_hashagg TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test3cs);
+ QUERY PLAN
+------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Unique
+ -> Sort
+ Sort Key: test3cs.x COLLATE case_insensitive
+ -> Index Only Scan using test3cs_x_idx on test3cs
+ -> Index Only Scan using test1ci_x_idx on test1ci
+ Index Cond: (x = (test3cs.x)::text)
+(8 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test3cs);
+ count
+-------
+ 3
+(1 row)
+
+SET LOCAL enable_hashagg TO on;
+SET LOCAL enable_groupagg TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test3cs);
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> HashAggregate
+ Group Key: (test3cs.x)::text
+ -> Index Only Scan using test3cs_x_idx on test3cs
+ -> Index Only Scan using test1ci_x_idx on test1ci
+ Index Cond: (x = (test3cs.x)::text)
+(7 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test3cs);
+ count
+-------
+ 3
+(1 row)
+
+ROLLBACK;
-- These queries should be able to use the index on test1ci.x:
SET enable_seqscan = off;
SET enable_indexonlyscan = off;
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index e96ad737aae..f0d9ee2c96d 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -751,6 +751,33 @@ CREATE UNIQUE INDEX ON test3ci (x); -- error
SELECT string_to_array('ABC,DEF,GHI' COLLATE case_insensitive, ',', 'abc');
SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b');
+-- Unique-ifying a semijoin's RHS must use the join's collation. test3cs
+-- holds both 'abc' and 'ABC', so test1ci's 'abc' must come out once.
+BEGIN;
+
+SET LOCAL enable_seqscan TO off;
+SET LOCAL enable_material TO off;
+SET LOCAL enable_hashjoin TO off;
+SET LOCAL enable_mergejoin TO off;
+SET LOCAL enable_hashagg TO off;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test3cs);
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test3cs);
+
+SET LOCAL enable_hashagg TO on;
+SET LOCAL enable_groupagg TO off;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test3cs);
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test3cs);
+
+ROLLBACK;
+
-- These queries should be able to use the index on test1ci.x:
SET enable_seqscan = off;
SET enable_indexonlyscan = off;