Re: BUG #19633: Unexpected results of IN (subquery) with a non-deterministic collation

чт, 20 авг. 2026 г. в 23:41, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> The following bug has been logged on the website:
>
> Bug reference: 19633
> Logged by: Suyang Zhong
> Email address: syzhong16(at)gmail(dot)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