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.4bb89f56@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.
From 2a22494c9938b2baf499f06e4ea8ed23b5468792 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Tue, 27 Oct 2026 20:08:45 +0100
Subject: [PATCH] Add test for ICU digit reorder bug
---
.../regress/expected/collate.icu.utf8.out | 36 +++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 18 ++++++++++
2 files changed, 54 insertions(+)
diff --git a/src/test/regress/expected/collate.icu.utf8.out
b/src/test/regress/expected/collate.icu.utf8.out
index 69b2f5109ad..91bd5718a45 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1265,6 +1265,42 @@ SELECT 'Goldmann' < 'Götz' COLLATE "de-x-icu",
'Goldmann' > 'Götz' COLLATE tes
t | t
(1 row)
+-- Test that reordering the "digit" reordering group is applied consistently
+-- by comparisons and by sorting (which may use sort keys). In some ICU
+-- versions, comparisons ignored the reordering while sort keys honored it
+-- (ICU-23016).
+CREATE COLLATION testcoll_digitslast (provider = icu, locale =
'und-u-kr-latn-digit');
+CREATE TABLE test_digitslast (a text COLLATE testcoll_digitslast);
+INSERT INTO test_digitslast VALUES ('1'), ('2'), ('a'), ('b'), ('A'), ('B');
+-- The sorted position of each value must agree with direct comparisons.
+SELECT bool_and(consistent) FROM
+ (SELECT row_number() OVER (ORDER BY a) =
+ 1 + (SELECT count(*) FROM test_digitslast u WHERE u.a < x.a)
+ AS consistent
+ FROM test_digitslast x) s;
+ bool_and
+----------
+ t
+(1 row)
+
+SELECT a FROM test_digitslast ORDER BY a; -- letters sort before digits
+ a
+---
+ a
+ A
+ b
+ B
+ 1
+ 2
+(6 rows)
+
+SELECT 'a' < '1' COLLATE testcoll_digitslast; -- true
+ ?column?
+----------
+ t
+(1 row)
+
+DROP TABLE test_digitslast;
-- rules
CREATE COLLATION testcoll_rules1 (provider = icu, locale = '', rules = '&a <
g');
NOTICE: using standard form "und" for ICU locale ""
diff --git a/src/test/regress/sql/collate.icu.utf8.sql
b/src/test/regress/sql/collate.icu.utf8.sql
index 87cea263780..8e1495a9011 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -501,6 +501,24 @@ CREATE COLLATION testcoll_de_phonebook (provider = icu,
locale = 'de@collation=p
SELECT 'Goldmann' < 'Götz' COLLATE "de-x-icu", 'Goldmann' > 'Götz' COLLATE
testcoll_de_phonebook;
+-- Test that reordering the "digit" reordering group is applied consistently
+-- by comparisons and by sorting (which may use sort keys). In some ICU
+-- versions, comparisons ignored the reordering while sort keys honored it
+-- (ICU-23016).
+CREATE COLLATION testcoll_digitslast (provider = icu, locale =
'und-u-kr-latn-digit');
+CREATE TABLE test_digitslast (a text COLLATE testcoll_digitslast);
+INSERT INTO test_digitslast VALUES ('1'), ('2'), ('a'), ('b'), ('A'), ('B');
+-- The sorted position of each value must agree with direct comparisons.
+SELECT bool_and(consistent) FROM
+ (SELECT row_number() OVER (ORDER BY a) =
+ 1 + (SELECT count(*) FROM test_digitslast u WHERE u.a < x.a)
+ AS consistent
+ FROM test_digitslast x) s;
+SELECT a FROM test_digitslast ORDER BY a; -- letters sort before digits
+SELECT 'a' < '1' COLLATE testcoll_digitslast; -- true
+DROP TABLE test_digitslast;
+
+
-- rules
CREATE COLLATION testcoll_rules1 (provider = icu, locale = '', rules = '&a <
g');
--
2.56.0
