Re: ICU fixed digit reordering bug

> On Oct 28, 2026, at 03:50, Peter Eisentraut <peter(at)eisentraut(dot)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:
>
> - 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.
>
> <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/