| From: | Peter Eisentraut <peter(at)eisentraut(dot)org> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | ICU fixed digit reordering bug |
| Date: | 2026-10-27 19:50:53 |
| Message-ID: | 7183c130-c4c8-45d0-8af8-b43b47c65f0a@eisentraut.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
There have been various reports over time that ICU collations that
reorder digits are buggy. These are collations defined like
CREATE COLLATION digitslast (provider = icu, locale = 'en-u-kr-latn-digit');
(which means, move digits after Latin letters). Example reports:
- https://www.postgresql.org/message-id/flat/20200715155220(dot)4bb89f56(at)firost
-
https://www.postgresql.org/message-id/flat/153201618542.1404.3611626898935613264%40wrigleys.postgresql.org
-
https://www.postgresql.org/message-id/7461f00ebf361d41731df5b913a35b0b%40postgrespro.ru
Some of these reports probably came because this was an example in our
documentation. Commit 4fff515e9ef replaced the example with something
non-buggy.
This issue was reported upstream to ICU:
https://unicode-org.atlassian.net/browse/ICU-23016
This issue was finally fixed in ICU 79, which came out a few days ago.
I have confirmed that the PostgreSQL issue is fixed with that new version.
The question now is whether there is anything else we want to do about this?
To briefly explain the issue and mechanism.
The problem is that this specific type of customized ICU locale with
digit reordering behaves inconsistently across different ICU APIs.
ucol_getSortKey() and ucol_nextSortKeyPart() handle it correctly, but
ucol_strcoll() and ucol_strcollUTF8() don't because they ignore the
digit reordering. PostgreSQL uses the former pair for abbreviated keys
and the latter for other comparisons, and so depending on what kinds of
plans and operations you get for a particular query or command, you get
inconsistent results. And specifically you get inconsistent indexes and
wrong query results.
The attached patch contains a regression test case that will pass if
PostgreSQL is built against ICU 79 but will fail with ICU 78.
Here are some things we could do:
0. Do nothing. Leave the ICU bug and possibly this email as reference
and recommend that users don't use the affected kinds of ICU locales
with the affected older ICU versions.
1. We could check when a ICU collator object is created
(create_pg_locale_icu()) whether the locale contains digits reordering
and error out if an affected older ICU version is used.
1.a) The same but only for future PostgreSQL versions (no backpatch).
2. For affected ICU locales and ICU versions, disable abbreviated keys.
This would use the existing struct collate_methods.strxfrm_is_safe
mechanism, since it's essentially the same kind of thing. This would
mean for those ICU versions digit reordering is silently skipped, but
the behavior becomes consistent across all sorting and comparison
operations, and existing indexes would became consistent.
3. For affected ICU locales and ICU versions, route comparisons through
a separate set of collate_methods functions that use the ICU sort keys
API. This would make the digits reordering actually work. But affected
indexes would need to be rebuilt. (A patch for this was provided in one
of the threads above.)
The last two are probably too much effort to make something work that
never worked to begin with. But I think it's worth contemplating
whether we want to do item 1, check explicitly for this bug and prevent
users running into it by accident. This is feasible now that we know
the precise cause and fix.
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Add-test-for-ICU-digit-reorder-bug.patch | text/plain | 3.3 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2026-10-27 19:57:02 | Re: [DEPARSE] A COLLATE clause in the URI of XMLNAMESPACES does not read back |
| Previous Message | Álvaro Herrera | 2026-10-27 19:42:18 | Re: Request to expedite commitfest account cool-off |