ICU fixed digit reordering bug

Started by Peter Eisentraut26 days ago6 messageshackers
Jump to latest
#1Peter Eisentraut
peter@eisentraut.org

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:

- /messages/by-id/20200715155220.4bb89f56@firost
-
/messages/by-id/153201618542.1404.3611626898935613264@wrigleys.postgresql.org
-
/messages/by-id/7461f00ebf361d41731df5b913a35b0b@postgrespro.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.

Attachments:

t254415_1
0001-Add-test-for-ICU-digit-reorder-bug.patchtext/plain; charset=UTF-8; name=0001-Add-test-for-ICU-digit-reorder-bug.patchDownload+54-1
#2Laurenz Albe
laurenz.albe@cybertec.at
In reply to: Peter Eisentraut (#1)
Re: ICU fixed digit reordering bug

On Tue, 2026-10-27 at 20:50 +0100, Peter Eisentraut wrote:

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).

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?

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).

[...]

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.

Option 1 is appealing, but it would prevent upgrading a database with
such a collation. That wouldn't be good.

Perhaps a warning would be good enough?

Yours,
Laurenz Albe

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Peter Eisentraut (#1)
Re: ICU fixed digit reordering bug

Peter Eisentraut <peter@eisentraut.org> writes:

There have been various reports over time that ICU collations that
reorder digits are buggy. ...
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?

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.

I like option 0. It can't be incumbent on us to provide defenses
for every bug in every piece of software that we depend on ...
especially not edge-case bugs like this one seems to be.

If we had a surplus of time and people-power available, maybe it
would be reasonable to do something about this. But you know as
well as I do how horribly short we are on both.

regards, tom lane

#4Chao Li
li.evan.chao@gmail.com
In reply to: Peter Eisentraut (#1)
Re: ICU fixed digit reordering bug

On Oct 28, 2026, at 03:50, Peter Eisentraut <peter@eisentraut.org> wrote:

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:

- /messages/by-id/20200715155220.4bb89f56@firost
- /messages/by-id/153201618542.1404.3611626898935613264@wrigleys.postgresql.org
- /messages/by-id/7461f00ebf361d41731df5b913a35b0b@postgrespro.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.

<0001-Add-test-for-ICU-digit-reorder-bug.patch>

I recently worked on a similar issue with zlib 1.3.2, where a bug in a third-party library impacts PG's correctness. In that case, PG happened to have its own implementation of the affected functionality, so I proposed a patch [1] to replace the buggy API with PG's own implementation.

However, I don't think changing PG to work around third-party library bugs is a scalable solution. Third-party libraries may introduce unpredictable bugs in the future, and there is no guarantee that PG can always provide its own workaround. Even when a workaround is possible, it introduces additional code and maintenance costs.

I'm thinking that maybe we could provide a general SQL function, say pg_check_libs(), which lists the versions of third-party libraries used by the current PG cluster and warns about known problematic versions.

The list of known issues would need to be maintained manually, but I think other solutions, such as detecting affected library versions and implementing workarounds, have the same maintenance requirement.

The list could also be maintained in PG's documentation. By reading the documentation, users could learn about all known third-party library issues. By running pg_check_libs(), users could check whether any of these known issues potentially affect their current cluster.

This could also encourage users to develop a habit of regularly checking their third-party library versions, especially after a system upgrade, such as upgrading the OS or PG.

Of course, this doesn't mean we should never implement PG-side workarounds. But it would provide a general mechanism for informing users about known third-party library problems, especially when implementing a workaround is difficult or impractical.

This is just a rough idea, and I'd propose it as option 4. If we go along with this approach, there would be more details to figure out.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

#5Haibo Yan
tristan.yim@gmail.com
In reply to: Tom Lane (#3)
Re: ICU fixed digit reordering bug

On Tue, Oct 27, 2026 at 2:40 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Eisentraut <peter@eisentraut.org> writes:

There have been various reports over time that ICU collations that
reorder digits are buggy. ...
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?

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.

I like option 0. It can't be incumbent on us to provide defenses
for every bug in every piece of software that we depend on ...
especially not edge-case bugs like this one seems to be.

If we had a surplus of time and people-power available, maybe it
would be reasonable to do something about this. But you know as
well as I do how horribly short we are on both.

regards, tom lane

One upgrade concern may be worth calling out: if a PostgreSQL installation
moves to ICU 79, comparisons for a digit-reordering collation will use the
corrected behavior, while an existing index may have been built under the
old behavior. That could leave the index inconsistent with current comparisons
and lead to wrong query results until it is rebuilt.

Should we explicitly advise users of affected collations to REINDEX after
upgrading ICU? It would also be useful to confirm whether the collation
version check reliably warns for this particular fix.

Regards,
Haibo

#6Peter Eisentraut
peter@eisentraut.org
In reply to: Tom Lane (#3)
Re: ICU fixed digit reordering bug

On 27.10.26 22:39, Tom Lane wrote:

Peter Eisentraut <peter@eisentraut.org> writes:

There have been various reports over time that ICU collations that
reorder digits are buggy. ...
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?

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.

I like option 0. It can't be incumbent on us to provide defenses
for every bug in every piece of software that we depend on ...
especially not edge-case bugs like this one seems to be.

Ok. This email thread can serve as something to point to if this comes
up again.