Wrong results from inner-unique joins caused by collation mismatch
Richard Guo <guofenglinux@gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-04-24T11:42:28Z
Lists: pgsql-hackers
Attachments
- v1-0001-Consider-collation-when-proving-uniqueness-from-u.patch (application/octet-stream) patch v1-0001
While working on the wrong results issue caused by collation mismatch
in GROUP BY and havingQual [1], I noticed $subject:
create collation ci (provider = icu, locale = 'und-u-ks-level2',
deterministic = false);
create table t (a text);
insert into t values ('A'), ('a');
create unique index on t (a);
-- wrong results: should be 4 rows
select * from t t1 join t t2 on t1.a = t2.a collate ci;
a | a
---+---
A | A
a | a
(2 rows)
The root cause is explained by the XXX comment in
relation_has_unique_index_for():
/*
* XXX at some point we may need to check collations here too.
* For the moment we assume all collations reduce to the same
* notion of equality.
*/
That assumption stopped being safe when nondeterministic collations
were introduced in PG 12. A unique index enforces uniqueness under
its own collation; if a query's equality clause uses a different
collation, and either side is nondeterministic, the index's uniqueness
does not imply uniqueness under the clause.
Several planner optimizations use this uniqueness proof, and all of
them can yield wrong results in this scenario. These include
inner-unique join execution, left-join removal, semijoin-to-innerjoin
reduction, and self-join elimination.
My first thought was to fix this by:
+ if (!IndexCollMatchesExprColl(ind->indexcollations[c],
+ exprInputCollation((Node *) rinfo->clause)))
+ continue;
However, this caused an unexpected plan diff in join.out where a
left-join removal over (name, text) stopped working, because name and
text use different collations. So this check is too strict: a
mismatch between two deterministic collations should be OK for
uniqueness proof, as a deterministic collation treats two strings as
equal iff they are byte-wise equal (see CREATE COLLATION).
Hence, I got attached patch. Thoughts?
[1] https://postgr.es/m/CAMbWs48Dn2wW6XM94GZsoyMiH42=KgMo+WcobPKuWvGYnWaPOQ@mail.gmail.com
- Richard
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Consider collation when proving subquery uniqueness
- 172034f6e088 14.23 landed
- bab4f7fa5621 15.18 landed
- 5a24cef082a0 16.14 landed
- 13226050e85d 17.10 landed
- bed3ffbf9d95 18.4 landed
- 574581b50ac9 19 (unreleased) landed
-
Consider collation when proving uniqueness from unique indexes
- 8395446dff08 14.23 landed
- 872c9fae78bc 15.18 landed
- 748fe9e6085c 16.14 landed
- d0e73bb18017 17.10 landed
- b62f514ac533 18.4 landed
- 5a55ea507a2d 19 (unreleased) landed