The following bug has been logged on the website:
Bug reference: 18368
Logged by: Octavian-Codrut Popescu
Email address: octavian@codrut-popescu.com
PostgreSQL version: 16.2
Operating system: Linux
Description:
codrut=# create collation german_custom_codrut(provider = icu, locale =
'de_DE.utf8@colReorder=latn-digit');
NOTICE: using standard form "de-DE-u-kr-latn-digit" for ICU locale
"de_DE.utf8@colReorder=latn-digit"
CREATE COLLATION
codrut=#
codrut=#
codrut=# with v (a, b) as (values ('83000003', '123abc'), ('83000003',
'abc123'), ('83000004', 'ABC123'))
codrut-# select a,b from v order by a, b collate "german_custom_codrut";
a | b
----------+--------
83000003 | 123abc
83000003 | abc123
83000004 | ABC123
(3 rows)
codrut=# with v (a, b) as (values ('83000003', '123abc'), ('83000003',
'abc123'), ('83000004', 'ABC123'))
codrut-# select a,b from v where a = '83000003' order by a, b collate
"german_custom_codrut";
a | b
----------+--------
83000003 | abc123
83000003 | 123abc
(2 rows)
codrut=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.2 (Postgres.app) on aarch64-apple-darwin21.6.0, compiled by
Apple clang version 14.0.0 (clang-1400.0.29.102), 64-bit
(1 row)
basically just introducing or removing where a = '83000003' changes the
ORDER BY behavior.