agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
11+ messages / 5 participants
[nested] [flat]
* BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
@ 2026-08-20 03:49 PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: PG Bug reporting form @ 2026-08-20 03:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: syzhong16@gmail.com
The following bug has been logged on the website:
Bug reference: 19633
Logged by: Suyang Zhong
Email address: syzhong16@gmail.com
PostgreSQL version: 19beta3
Operating system: Ubuntu 22.04
Description:
Hi,
Consider the following test case:
```
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level1',
deterministic = false);
CREATE TABLE t_lhs(c1 text COLLATE ci);
CREATE TABLE t_rhs(c0 text);
INSERT INTO t_lhs VALUES ('a'), ('x'), ('y');
INSERT INTO t_rhs VALUES ('a'), ('a');
ANALYZE t_lhs;
ANALYZE t_rhs;
INSERT INTO t_rhs VALUES ('A');
SELECT c1, c1 IN (SELECT c0 FROM t_rhs) AS p FROM t_lhs;
-- a | t
-- x | f
-- y | f
SELECT count(*) FROM t_lhs WHERE c1 IN (SELECT c0 FROM t_rhs);
-- Expected: 1, Actual: 2
```
The predicate evaluates to true for one row, so filtering on the same
predicate should return one row.
Reproduced on 20devel and 19beta3.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
@ 2026-08-20 20:34 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Tender Wang <tndrwang@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Andrey Rachitskiy @ 2026-08-20 20:34 UTC (permalink / raw)
To: syzhong16@gmail.com, pgsql-bugs@lists.postgresql.org, Alexander Korotkov <aekorotkov@gmail.com>; Tender Wang <tndrwang@gmail.com>
чт, 20 авг. 2026 г. в 23:41, PG Bug reporting form <noreply@postgresql.org>:
> The following bug has been logged on the website:
>
> Bug reference: 19633
> Logged by: Suyang Zhong
> Email address: syzhong16@gmail.com
> PostgreSQL version: 19beta3
> Operating system: Ubuntu 22.04
> Description:
>
> Hi,
>
> Consider the following test case:
>
> ```
> CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level1',
> deterministic = false);
>
> CREATE TABLE t_lhs(c1 text COLLATE ci);
> CREATE TABLE t_rhs(c0 text);
> INSERT INTO t_lhs VALUES ('a'), ('x'), ('y');
> INSERT INTO t_rhs VALUES ('a'), ('a');
> ANALYZE t_lhs;
> ANALYZE t_rhs;
> INSERT INTO t_rhs VALUES ('A');
>
> SELECT c1, c1 IN (SELECT c0 FROM t_rhs) AS p FROM t_lhs;
> -- a | t
> -- x | f
> -- y | f
>
> SELECT count(*) FROM t_lhs WHERE c1 IN (SELECT c0 FROM t_rhs);
> -- Expected: 1, Actual: 2
> ```
>
> Hi, Suyang!
Thanks for the report.
With enable_hashagg off that looks like:
Aggregate
-> Nested Loop
Join Filter: (t_semi_ci.c1 = ((t_semi_cs.c0)::text))
-> Unique
-> Sort
Sort Key: t_semi_cs.c0
-> Seq Scan on t_semi_cs
-> Seq Scan on t_semi_ci
Under gdb that path is create_unique_paths from the join search:
#0 create_unique_paths at planner.c:8673
#1 populate_joinrel_with_paths at joinrels.c:1189
#2 make_join_rel at joinrels.c:774
#3 make_rels_by_clause_joins at joinrels.c:300
#4 join_search_one_level at joinrels.c:123
#5 standard_join_search at allpaths.c:3987
(gdb) pgprint sjinfo
SpecialJoinInfo [jointype=JOIN_SEMI semi_can_btree=true
semi_can_hash=false]
[semi_operators] OidList: [98]
[semi_rhs_exprs]
Var [varno=3 varattno=1 vartype=25 varcollid=100]
Unique/HashAgg take the collation from the RHS expression. Here that
is the default collation of t_rhs.c0 (varcollid 100), not the join's
input collation (ci). So Sort+Unique keeps both 'a' and 'A'. Under
ci those values are equal, and the inner join emits the outer 'a'
twice (count is 2).
The attached patch labels each semi_rhs_expr with the join operator's
inputcollid via canonicalize_ec_expression (RelabelType when needed),
so unique-ification uses the same equality as the join. Sort then
shows
Sort Key: t_semi_cs.c0 COLLATE case_insensitive
and the count is 1. A regress case is included in collate.icu.utf8.
I am still getting familiar with this part. I am not sure this is the right
place or the right approach.
Thoughts?
--
Regards,
Rachitskiy Andrey
Attachments:
[text/x-patch] 0001-Fix-semijoin-RHS-unique-ification-to-use-join-collation.patch (4.3K, ../../CAB8bMiuXGnBC3QrjpTK89MjweEP+Znc4MJNc6W-2k94AFv_TMQ@mail.gmail.com/3-0001-Fix-semijoin-RHS-unique-ification-to-use-join-collation.patch)
download | inline diff:
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Thu, 20 Aug 2026 19:15:00 +0000
Subject: [PATCH] Fix semijoin RHS unique-ification to use join collation
When unique-ifying a semijoin RHS for JOIN_UNIQUE_*, pathkeys and Unique
take collation from the RHS expression (SortGroupClause does not carry
one). That can be a different (deterministic) collation than the join's
inputcollid. Unique then leaves values that are still equal under the
join, and the subsequent INNER join duplicates outer rows.
Force each semi_rhs_expr to expose the join operator's input collation
via canonicalize_ec_expression (RelabelType).
BUG #19633
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reported-by: Suyang Zhong <syzhong16@gmail.com>
Discussion: https://www.postgresql.org/message-id/19633-647cd4c73a84b085@postgresql.org
---
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index f08a918146c..fa98e7ab162 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2628,7 +2628,17 @@ 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));
+
+ /*
+ * Unique-ification takes collation from the expression, so label
+ * the RHS with the join's input collation (RelabelType if needed).
+ * Otherwise Unique may keep values that are still equal under the
+ * join, and JOIN_UNIQUE_* (as INNER) will duplicate outer rows.
+ */
+ 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 fcfcc658bea..a7ca794a0a2 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1988,6 +1988,35 @@ ORDER BY 1;
ghi
(4 rows)
+-- Unique-ification of an IN/semijoin RHS must use the join collation.
+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;
+INSERT INTO t_semi_cs VALUES ('A');
+SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs);
+ count
+-------
+ 1
+(1 row)
+
+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)
+
+RESET enable_hashagg;
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 ce4e2bb3ffd..b4994c64262 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -721,6 +721,19 @@ SELECT * FROM test3cs t1
WHERE t1.x = t2.x COLLATE case_insensitive)
ORDER BY 1;
+-- Unique-ification of an IN/semijoin RHS must use the join collation.
+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;
+INSERT INTO t_semi_cs VALUES ('A');
+SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs);
+SET enable_hashagg TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs);
+RESET enable_hashagg;
+
CREATE TABLE test1ci (x text COLLATE case_insensitive);
CREATE TABLE test2ci (x text COLLATE case_insensitive);
CREATE TABLE test3ci (x text COLLATE case_insensitive);
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-08-21 01:54 ` Tender Wang <tndrwang@gmail.com>
2026-08-21 05:01 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tender Wang @ 2026-08-21 01:54 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: syzhong16@gmail.com, pgsql-bugs@lists.postgresql.org, Alexander Korotkov <aekorotkov@gmail.com>
Hi Andrey,
Andrey Rachitskiy <pl0h0yp1@gmail.com> 于2026年8月21日周五 04:35写道：
>
>
> чт, 20 авг. 2026 г. в 23:41, PG Bug reporting form <noreply@postgresql.org>:
> Under gdb that path is create_unique_paths from the join search:
>
> #0 create_unique_paths at planner.c:8673
> #1 populate_joinrel_with_paths at joinrels.c:1189
> #2 make_join_rel at joinrels.c:774
> #3 make_rels_by_clause_joins at joinrels.c:300
> #4 join_search_one_level at joinrels.c:123
> #5 standard_join_search at allpaths.c:3987
>
> (gdb) pgprint sjinfo
> SpecialJoinInfo [jointype=JOIN_SEMI semi_can_btree=true
> semi_can_hash=false]
> [semi_operators] OidList: [98]
> [semi_rhs_exprs]
> Var [varno=3 varattno=1 vartype=25 varcollid=100]
>
> Unique/HashAgg take the collation from the RHS expression. Here that
> is the default collation of t_rhs.c0 (varcollid 100), not the join's
> input collation (ci). So Sort+Unique keeps both 'a' and 'A'. Under
> ci those values are equal, and the inner join emits the outer 'a'
> twice (count is 2).
>
> The attached patch labels each semi_rhs_expr with the join operator's
> inputcollid via canonicalize_ec_expression (RelabelType when needed),
> so unique-ification uses the same equality as the join. Sort then
> shows
>
> Sort Key: t_semi_cs.c0 COLLATE case_insensitive
>
> and the count is 1. A regress case is included in collate.icu.utf8.
>
> I am still getting familiar with this part. I am not sure this is the right place or the right approach.
>
> Thoughts?
The approach looks good to me. I'd suggest adjusting the comment as follows:
...
/* so far so good, keep building lists */
semi_operators = lappend_oid(semi_operators, opno);
/*
* Ensure that the RHS expression exposes the join operator's input
* collation. The expression will later be used as a grouping key when
* unique-ifying the RHS, so its collation must agree with the semijoin
* equality semantics.
*/
semi_rhs_exprs =
lappend(semi_rhs_exprs,
canonicalize_ec_expression((Expr *) copyObject(right_expr),
exprType(right_expr),
op->inputcollid));
...
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Tender Wang <tndrwang@gmail.com>
@ 2026-08-21 05:01 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-09 22:39 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Andrey Rachitskiy @ 2026-08-21 05:01 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: syzhong16@gmail.com, pgsql-bugs@lists.postgresql.org, Alexander Korotkov <aekorotkov@gmail.com>
пт, 21 авг. 2026 г. в 06:54, Tender Wang <tndrwang@gmail.com>:
> The approach looks good to me. I'd suggest adjusting the comment as
> follows:
> ...
> /* so far so good, keep building lists */
> semi_operators = lappend_oid(semi_operators, opno);
>
> /*
> * Ensure that the RHS expression exposes the join operator's input
> * collation. The expression will later be used as a grouping key when
> * unique-ifying the RHS, so its collation must agree with the semijoin
> * equality semantics.
> */
> semi_rhs_exprs =
> lappend(semi_rhs_exprs,
> canonicalize_ec_expression((Expr *) copyObject(right_expr),
> exprType(right_expr),
> op->inputcollid));
>
> Dear Tender,
Thanks for the review.
Made the changes - v2 attached.
--
Regards,
Rachitskiy Andrey
Attachments:
[text/x-patch] v2-0001-Fix-semijoin-RHS-unique-ification-to-use-join-collation.patch (4.6K, ../../CAB8bMivfsjkq_kG3VehogS8-PNMMxuVdprpXKMPxoEyh6WS-Rw@mail.gmail.com/3-v2-0001-Fix-semijoin-RHS-unique-ification-to-use-join-collation.patch)
download | inline diff:
From c59dd93bc3510c9c20ddc4097ba42de20c113000 Mon Sep 17 00:00:00 2001
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Fri, 21 Aug 2026 09:53:58 +0500
Subject: [PATCH] Fix semijoin RHS unique-ification to use join collation
When unique-ifying a semijoin RHS for JOIN_UNIQUE_*, pathkeys and Unique
take collation from the RHS expression (SortGroupClause does not carry
one). That can be a different (deterministic) collation than the join's
inputcollid. Unique then leaves values that are still equal under the
join, and the subsequent INNER join duplicates outer rows.
Force each semi_rhs_expr to expose the join operator's input collation
via canonicalize_ec_expression (RelabelType).
BUG #19633
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reported-by: Suyang Zhong <syzhong16@gmail.com>
Discussion: https://www.postgresql.org/message-id/19633-647cd4c73a84b085@postgresql.org
---
src/backend/optimizer/plan/initsplan.c | 12 +++++++-
.../regress/expected/collate.icu.utf8.out | 29 +++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 13 +++++++++
3 files changed, 53 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index f08a918146c..20834d7fcc7 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2628,7 +2628,17 @@ 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. The expression will later be used as a grouping key when
+ * unique-ifying the RHS, so its collation must agree with the semijoin
+ * equality semantics.
+ */
+ 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 fcfcc658bea..a7ca794a0a2 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1988,6 +1988,35 @@ ORDER BY 1;
ghi
(4 rows)
+-- Unique-ification of an IN/semijoin RHS must use the join collation.
+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;
+INSERT INTO t_semi_cs VALUES ('A');
+SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs);
+ count
+-------
+ 1
+(1 row)
+
+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)
+
+RESET enable_hashagg;
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 ce4e2bb3ffd..b4994c64262 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -721,6 +721,19 @@ SELECT * FROM test3cs t1
WHERE t1.x = t2.x COLLATE case_insensitive)
ORDER BY 1;
+-- Unique-ification of an IN/semijoin RHS must use the join collation.
+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;
+INSERT INTO t_semi_cs VALUES ('A');
+SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs);
+SET enable_hashagg TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM t_semi_ci WHERE c1 IN (SELECT c0 FROM t_semi_cs);
+RESET enable_hashagg;
+
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.53.0
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Tender Wang <tndrwang@gmail.com>
2026-08-21 05:01 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-09-09 22:39 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-09-10 00:10 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Alexander Korotkov @ 2026-09-09 22:39 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
Hi!
On Fri, Aug 21, 2026 at 8:02 AM Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
> пт, 21 авг. 2026 г. в 06:54, Tender Wang <tndrwang@gmail.com>:
>>
>> The approach looks good to me. I'd suggest adjusting the comment as follows:
>> ...
>> /* so far so good, keep building lists */
>> semi_operators = lappend_oid(semi_operators, opno);
>>
>> /*
>> * Ensure that the RHS expression exposes the join operator's input
>> * collation. The expression will later be used as a grouping key when
>> * unique-ifying the RHS, so its collation must agree with the semijoin
>> * equality semantics.
>> */
>> semi_rhs_exprs =
>> lappend(semi_rhs_exprs,
>> canonicalize_ec_expression((Expr *) copyObject(right_expr),
>> exprType(right_expr),
>> op->inputcollid));
>>
> Dear Tender,
>
> Thanks for the review.
> Made the changes - v2 attached.
I think this approach is right. Everybody who consumes
SpecialJoinInfo.semi_rhs_exprs takes the collation from the expression
itself, because SortGroupClause does not carry one and neither does
the pathkey machinery. So labelling the expression once, where it is
recorded, fixes every consumer at the same time; fixing it in the
consumers would mean doing the same thing in three places and getting
it right in each. The call is also exactly what process_equivalence()
already does for equivalence class members, with the same two
arguments and for the same reason, so this is not a new trick.
I've revised the patch. The v3 changes are:
1. The test now covers the hash path as well. v2 turned
enable_hashagg off, which is precisely the plan shape that was not
broken in isolation; both strategies are now exercised, each with its
plan and its result.
2. Test also checks results rows, not only the count. The bug is the
disagreement between the two: the predicate is true for one row while
filtering on it returned two.
3. Added a test for unique-ification being skipped because of a unique
index. Getting real coverage from it needs a little care: with only
'a' and 'A' in the RHS the planner keeps an ordinary semijoin and
answers correctly, so the test passes with or without the fix. Two
filler rows are enough to make unique-ification look worthwhile and
put the decision on the table. The plan is checked as well, so that a
future costing change cannot quietly turn this into a test of nothing.
4. Added a comment saying why the INSERT of 'A' comes after ANALYZE.
5. Reworded the code comment to say why rather than what:
unique-ification groups on this expression and takes the collation
from it, since SortGroupClause carries none.
6. Commit message now mentions HashAggregate, which v2 did not.
Now the part that is missing from the thread entirely. This is not a v19
regression. The line
semi_rhs_exprs = lappend(semi_rhs_exprs, copyObject(right_expr));
is identical in every supported branch back to 14, and the consumer
has the same shape there: REL_18's create_unique_path() builds a
SortGroupClause with no collation and takes the key from uniqexpr. So
this is a live wrong-results bug in all of them (I've checked it), and
I intend to backpatch it through 14.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v3-0001-Use-the-join-collation-when-unique-ifying-a-semij.patch (9.5K, ../../CAPpHfdvCQ6FZ_3iq26zBCBCEAor0bE3-O9K1z8Upbwi8mLABtQ@mail.gmail.com/2-v3-0001-Use-the-join-collation-when-unique-ifying-a-semij.patch)
download | inline diff:
From 41a7e174ed2c396af9fdb327d112773ac796acfb Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
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 <syzhong16@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reviewed-by: Tender Wang <tndrwang@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 | 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Tender Wang <tndrwang@gmail.com>
2026-08-21 05:01 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-09 22:39 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-09-10 00:10 ` Richard Guo <guofenglinux@gmail.com>
2026-09-10 05:36 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Richard Guo @ 2026-09-10 00:10 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>; Tender Wang <tndrwang@gmail.com>; syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
On Thu, Sep 10, 2026 at 7:40 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> I've revised the patch. The v3 changes are:
> 1. The test now covers the hash path as well. v2 turned
> enable_hashagg off, which is precisely the plan shape that was not
> broken in isolation; both strategies are now exercised, each with its
> plan and its result.
I think it'd better to resue existing tables rather than create
new ones for the tests. Maybe we can reuse test1cs/test1ci,
test2cs/test2ci, test3cs/test3ci to reproduce this issue?
Also on master we have this new GUC enable_groupagg. Maybe it'd
better to use that instead of enable_sort?
- Richard
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Tender Wang <tndrwang@gmail.com>
2026-08-21 05:01 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-09 22:39 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
2026-09-10 00:10 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Richard Guo <guofenglinux@gmail.com>
@ 2026-09-10 05:36 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-10 07:56 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Andrey Rachitskiy @ 2026-09-10 05:36 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Korotkov <aekorotkov@gmail.com>; Tender Wang <tndrwang@gmail.com>; syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
чт, 10 сент. 2026 г. в 05:10, Richard Guo <guofenglinux@gmail.com>:
> On Thu, Sep 10, 2026 at 7:40 AM Alexander Korotkov <aekorotkov@gmail.com>
> wrote:
> > I've revised the patch. The v3 changes are:
> > 1. The test now covers the hash path as well. v2 turned
> > enable_hashagg off, which is precisely the plan shape that was not
> > broken in isolation; both strategies are now exercised, each with its
> > plan and its result.
>
> I think it'd better to resue existing tables rather than create
> new ones for the tests. Maybe we can reuse test1cs/test1ci,
> test2cs/test2ci, test3cs/test3ci to reproduce this issue?
>
fix in v4
> Also on master we have this new GUC enable_groupagg. Maybe it'd
> better to use that instead of enable_sort?
>
> This GUC exists only on master (e01b23b84e4, July 2026) and is not present
on REL_14_STABLE through REL_19_STABLE, so it is preferable to keep
enable_sort=off — that way the tests won't diverge from the backpatch.
--
Regards,
Rachitskiy Andrey
Attachments:
[text/x-patch] v4-0001-Use-the-join-collation-when-unique-ifying-a-semij.patch (10.1K, ../../CAB8bMityRZ7=_9NMsGqAff1YLYhd5R8ZBT7UaSUG9K_ck8+OPA@mail.gmail.com/3-v4-0001-Use-the-join-collation-when-unique-ifying-a-semij.patch)
download | inline diff:
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Thu, 10 Sep 2026 09:00:00 +0500
Subject: [PATCH v4] 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 <syzhong16@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com>
Discussion: https://postgr.es/m/19633-647cd4c73a84b085%40postgresql.org
Backpatch-through: 14
---
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..dd5ef982476 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2114,6 +2114,121 @@ SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b');
{A,NULL,C,D,E,F,G,H,I}
(1 row)
+-- 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.
+-- 'abc' collides with the existing 'ABC' only under case_insensitive.
+INSERT INTO test2cs VALUES ('abc');
+-- As-is the reused tables prefer a plain semi join. Pin n_distinct so
+-- unique-ification looks worthwhile and the plans below stay the ones we mean.
+ALTER TABLE test2cs ALTER COLUMN x SET (n_distinct = 1);
+ANALYZE test1ci, test2cs;
+-- The predicate holds for exactly one row, so filtering on it must return one.
+SELECT x, x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC')) AS p
+FROM test1ci ORDER BY 1;
+ x | p
+-----+---
+ abc | t
+ def | f
+ ghi | f
+(3 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ count
+-------
+ 1
+(1 row)
+
+-- Both unique-ification strategies must use the join collation.
+SET enable_hashagg TO off;
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (test1ci.x = ((test2cs.x)::text))
+ -> Unique
+ -> Sort
+ Sort Key: test2cs.x COLLATE case_insensitive
+ -> Seq Scan on test2cs
+ Filter: (x = ANY ('{abc,ABC}'::text[]))
+ -> Seq Scan on test1ci
+(9 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ count
+-------
+ 1
+(1 row)
+
+RESET enable_hashagg;
+RESET enable_hashjoin;
+RESET enable_mergejoin;
+SET enable_sort TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (test1ci.x = (test2cs.x)::text)
+ -> HashAggregate
+ Group Key: (test2cs.x)::text
+ -> Seq Scan on test2cs
+ Filter: (x = ANY ('{abc,ABC}'::text[]))
+ -> Seq Scan on test1ci
+(8 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ count
+-------
+ 1
+(1 row)
+
+RESET enable_sort;
+ALTER TABLE test2cs ALTER COLUMN x RESET (n_distinct);
+-- 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.
+INSERT INTO test1cs VALUES ('v1'), ('v2');
+ANALYZE test1cs;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test1cs);
+ QUERY PLAN
+----------------------------------------------------
+ Aggregate
+ -> Hash Right Semi Join
+ Hash Cond: ((test1cs.x)::text = test1ci.x)
+ -> Seq Scan on test1cs
+ -> Hash
+ -> Seq Scan on test1ci
+(6 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test1cs);
+ count
+-------
+ 3
+(1 row)
+
-- 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 df57ebc8bc8..fc45127f83e 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -751,6 +751,63 @@ 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-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.
+-- 'abc' collides with the existing 'ABC' only under case_insensitive.
+INSERT INTO test2cs VALUES ('abc');
+-- As-is the reused tables prefer a plain semi join. Pin n_distinct so
+-- unique-ification looks worthwhile and the plans below stay the ones we mean.
+ALTER TABLE test2cs ALTER COLUMN x SET (n_distinct = 1);
+ANALYZE test1ci, test2cs;
+-- The predicate holds for exactly one row, so filtering on it must return one.
+SELECT x, x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC')) AS p
+FROM test1ci ORDER BY 1;
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+
+-- Both unique-ification strategies must use the join collation.
+SET enable_hashagg TO off;
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+RESET enable_hashagg;
+RESET enable_hashjoin;
+RESET enable_mergejoin;
+
+SET enable_sort TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+RESET enable_sort;
+ALTER TABLE test2cs ALTER COLUMN x RESET (n_distinct);
+
+-- 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.
+INSERT INTO test1cs VALUES ('v1'), ('v2');
+ANALYZE test1cs;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test1cs);
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test1cs);
+
-- These queries should be able to use the index on test1ci.x:
SET enable_seqscan = off;
SET enable_indexonlyscan = off;
--
2.55.0
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Tender Wang <tndrwang@gmail.com>
2026-08-21 05:01 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-09 22:39 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
2026-09-10 00:10 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Richard Guo <guofenglinux@gmail.com>
2026-09-10 05:36 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-09-10 07:56 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-10 21:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Andrey Rachitskiy @ 2026-09-10 07:56 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Korotkov <aekorotkov@gmail.com>; Tender Wang <tndrwang@gmail.com>; syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
On backpatch the C change applies cleanly through 14. The regress
SQL hunks also apply, but the EXPLAIN text is not identical across
branches even when the counts are. I checked REL_19 through REL_14
with --with-icu. After the fix the counts are 1/1/1 and 3 everywhere.
The EXPLAIN differences are:
- Unique Sort Key on REL_18 and older prints
((test2cs.x)::text) COLLATE case_insensitive
where master/REL_19 print test2cs.x COLLATE case_insensitive
- unique-index case on REL_17 through REL_14 comes out as
Hash Semi Join rather than Hash Right Semi Join
So the backpatch will need a small expected.
--
Regards,
Rachitskiy Andrey
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Tender Wang <tndrwang@gmail.com>
2026-08-21 05:01 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-09 22:39 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
2026-09-10 00:10 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Richard Guo <guofenglinux@gmail.com>
2026-09-10 05:36 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-10 07:56 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-09-10 21:54 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-09-11 01:03 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Alexander Korotkov @ 2026-09-10 21:54 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: Richard Guo <guofenglinux@gmail.com>; Tender Wang <tndrwang@gmail.com>; syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
On Thu, Sep 10, 2026 at 10:56 AM Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
> On backpatch the C change applies cleanly through 14. The regress
> SQL hunks also apply, but the EXPLAIN text is not identical across
> branches even when the counts are. I checked REL_19 through REL_14
> with --with-icu. After the fix the counts are 1/1/1 and 3 everywhere.
> The EXPLAIN differences are:
>
> - Unique Sort Key on REL_18 and older prints
> ((test2cs.x)::text) COLLATE case_insensitive
> where master/REL_19 print test2cs.x COLLATE case_insensitive
>
> - unique-index case on REL_17 through REL_14 comes out as
> Hash Semi Join rather than Hash Right Semi Join
>
> So the backpatch will need a small expected.
OK, I'll take care about this during backpatching.
I also like idea of usage of enable_sort all the version in this case.
This would simplify backpatching and reduce divergence between
versions.
I made two changes to the patch.
1) Dropped the two filler rows in test1cs. They were needed back when
that block used a private two-row table; test1cs already has four
rows. That's enough to get the desired plan.
2) Dropped "ALTER TABLE test2cs ALTER COLUMN x RESET (n_distinct)".
With no ANALYZE after it, it does not restore the statistics anyway,
and nothing later in the file reads test2cs.
I plan to push this and backpatch through 14, adjusting the expected
file per branch along the lines you described.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v5-0001-Use-the-join-collation-when-unique-ifying-a-semij.patch (9.5K, ../../CAPpHfdsynTShGSUfXTuXNmqtExHOxvOBVW0Cto685Qm65tKPRA@mail.gmail.com/2-v5-0001-Use-the-join-collation-when-unique-ifying-a-semij.patch)
download | inline diff:
From 41a7e174ed2c396af9fdb327d112773ac796acfb Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Wed, 9 Sep 2026 13:35:29 +0300
Subject: [PATCH v5] 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 <syzhong16@gmail.com>
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reviewed-by: Tender Wang <tndrwang@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 | 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Tender Wang <tndrwang@gmail.com>
2026-08-21 05:01 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-09 22:39 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
2026-09-10 00:10 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Richard Guo <guofenglinux@gmail.com>
2026-09-10 05:36 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-10 07:56 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-10 21:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
@ 2026-09-11 01:03 ` Richard Guo <guofenglinux@gmail.com>
2026-09-11 05:06 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Richard Guo @ 2026-09-11 01:03 UTC (permalink / raw)
To: Alexander Korotkov <aekorotkov@gmail.com>; +Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>; Tender Wang <tndrwang@gmail.com>; syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, Sep 11, 2026 at 6:54 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> I also like idea of usage of enable_sort all the version in this case.
> This would simplify backpatching and reduce divergence between
> versions.
I believe one of the goals of commit e01b23b84 is replacing
enable_sort with enable_groupagg in tests going forward where that's
the real intent. Re-introducing enable_sort here seems to go against
that goal.
> I plan to push this and backpatch through 14, adjusting the expected
> file per branch along the lines you described.
Also I don't understand why v5 is still creating new tables rather
than using existing tables in the tests.
- Richard
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Tender Wang <tndrwang@gmail.com>
2026-08-21 05:01 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-09 22:39 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
2026-09-10 00:10 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Richard Guo <guofenglinux@gmail.com>
2026-09-10 05:36 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-10 07:56 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-10 21:54 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Alexander Korotkov <aekorotkov@gmail.com>
2026-09-11 01:03 ` Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation Richard Guo <guofenglinux@gmail.com>
@ 2026-09-11 05:06 ` Alexander Korotkov <aekorotkov@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Alexander Korotkov @ 2026-09-11 05:06 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>; Tender Wang <tndrwang@gmail.com>; syzhong16@gmail.com; pgsql-bugs@lists.postgresql.org
On Fri, Sep 11, 2026 at 4:03 AM Richard Guo <guofenglinux@gmail.com> wrote:
>
> On Fri, Sep 11, 2026 at 6:54 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> > I also like idea of usage of enable_sort all the version in this case.
> > This would simplify backpatching and reduce divergence between
> > versions.
>
> I believe one of the goals of commit e01b23b84 is replacing
> enable_sort with enable_groupagg in tests going forward where that's
> the real intent. Re-introducing enable_sort here seems to go against
> that goal.
>
> > I plan to push this and backpatch through 14, adjusting the expected
> > file per branch along the lines you described.
>
> Also I don't understand why v5 is still creating new tables rather
> than using existing tables in the tests.
Sorry, I accidentally used the wrong branch to generate the patch.
Please, check the attached version: it doesn't create new tables.
Also I changed usage of enable_sort to enable_groupagg as you
suggested. I will have to change it to enable_sort during
backpatching.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v6-0001-Use-the-join-collation-when-unique-ifying-a-semij.patch (10.0K, ../../CAPpHfdtQ_52yZkUSSHH0fy1m8MNuZoDUK_Y6FOjyT1Li9sLfHg@mail.gmail.com/2-v6-0001-Use-the-join-collation-when-unique-ifying-a-semij.patch)
download | inline diff:
From 9e66f3e45f701a5b76b8a8745432faa5171fa68f Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Thu, 10 Sep 2026 14:49:18 +0300
Subject: [PATCH v6] 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 <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 | 13 +-
.../regress/expected/collate.icu.utf8.out | 111 ++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 53 +++++++++
3 files changed, 176 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..003758f2153 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2114,6 +2114,117 @@ SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b');
{A,NULL,C,D,E,F,G,H,I}
(1 row)
+-- 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.
+-- 'abc' collides with the existing 'ABC' only under case_insensitive.
+INSERT INTO test2cs VALUES ('abc');
+-- As-is the reused tables prefer a plain semi join. Pin n_distinct so
+-- unique-ification looks worthwhile and the plans below stay the ones we mean.
+ALTER TABLE test2cs ALTER COLUMN x SET (n_distinct = 1);
+ANALYZE test1ci, test2cs;
+-- The predicate holds for exactly one row, so filtering on it must return one.
+SELECT x, x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC')) AS p
+FROM test1ci ORDER BY 1;
+ x | p
+-----+---
+ abc | t
+ def | f
+ ghi | f
+(3 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ count
+-------
+ 1
+(1 row)
+
+-- Both unique-ification strategies must use the join collation.
+SET enable_hashagg TO off;
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ QUERY PLAN
+-------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (test1ci.x = ((test2cs.x)::text))
+ -> Unique
+ -> Sort
+ Sort Key: test2cs.x COLLATE case_insensitive
+ -> Seq Scan on test2cs
+ Filter: (x = ANY ('{abc,ABC}'::text[]))
+ -> Seq Scan on test1ci
+(9 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ count
+-------
+ 1
+(1 row)
+
+RESET enable_hashagg;
+RESET enable_hashjoin;
+RESET enable_mergejoin;
+SET enable_groupagg TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (test1ci.x = (test2cs.x)::text)
+ -> HashAggregate
+ Group Key: (test2cs.x)::text
+ -> Seq Scan on test2cs
+ Filter: (x = ANY ('{abc,ABC}'::text[]))
+ -> Seq Scan on test1ci
+(8 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+ count
+-------
+ 1
+(1 row)
+
+RESET enable_groupagg;
+-- 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 plan is checked too, so that a costing
+-- change cannot quietly turn this into a test of nothing.
+ANALYZE test1cs;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test1cs);
+ QUERY PLAN
+----------------------------------------------------
+ Aggregate
+ -> Hash Right Semi Join
+ Hash Cond: ((test1cs.x)::text = test1ci.x)
+ -> Seq Scan on test1cs
+ -> Hash
+ -> Seq Scan on test1ci
+(6 rows)
+
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test1cs);
+ count
+-------
+ 3
+(1 row)
+
-- 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 df57ebc8bc8..00543398e3e 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -751,6 +751,59 @@ 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-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.
+-- 'abc' collides with the existing 'ABC' only under case_insensitive.
+INSERT INTO test2cs VALUES ('abc');
+-- As-is the reused tables prefer a plain semi join. Pin n_distinct so
+-- unique-ification looks worthwhile and the plans below stay the ones we mean.
+ALTER TABLE test2cs ALTER COLUMN x SET (n_distinct = 1);
+ANALYZE test1ci, test2cs;
+-- The predicate holds for exactly one row, so filtering on it must return one.
+SELECT x, x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC')) AS p
+FROM test1ci ORDER BY 1;
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+
+-- Both unique-ification strategies must use the join collation.
+SET enable_hashagg TO off;
+SET enable_hashjoin TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+RESET enable_hashagg;
+RESET enable_hashjoin;
+RESET enable_mergejoin;
+
+SET enable_groupagg TO off;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN
+ (SELECT x FROM test2cs WHERE x IN ('abc', 'ABC'));
+RESET enable_groupagg;
+
+-- 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 plan is checked too, so that a costing
+-- change cannot quietly turn this into a test of nothing.
+ANALYZE test1cs;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test1cs);
+SELECT count(*) FROM test1ci
+WHERE x COLLATE case_insensitive IN (SELECT x FROM test1cs);
+
-- These queries should be able to use the index on test1ci.x:
SET enable_seqscan = off;
SET enable_indexonlyscan = off;
--
2.55.0
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2026-09-11 05:06 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-20 03:49 BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation PG Bug reporting form <noreply@postgresql.org>
2026-08-20 20:34 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-08-21 01:54 ` Tender Wang <tndrwang@gmail.com>
2026-08-21 05:01 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-09 22:39 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-09-10 00:10 ` Richard Guo <guofenglinux@gmail.com>
2026-09-10 05:36 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-10 07:56 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-10 21:54 ` Alexander Korotkov <aekorotkov@gmail.com>
2026-09-11 01:03 ` Richard Guo <guofenglinux@gmail.com>
2026-09-11 05:06 ` Alexander Korotkov <aekorotkov@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox