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

Hi Andrey,

Andrey Rachitskiy <pl0h0yp1(at)gmail(dot)com> 于2026年8月21日周五 04:35写道：
>
>
> чт, 20 авг. 2026 г. в 23:41, PG Bug reporting form <noreply(at)postgresql(dot)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