ICU fixed digit reordering bug

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

Responses

Browse pgsql-hackers by date

  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