Обсуждение: Problem in 'ORDER BY' of a column using a created collation?

On Mon, Aug 25, 2025 at 3:52 PM Nishant Sharma
<nishant.sharma@enterprisedb.com> wrote:
>
>
> Experiment 1:-
> SQL File : PG_Exp_1.sql
>
> Actual Output : PG_Exp_1.out
>
> Created COLLATION : CREATE COLLATION test_coll (
> provider = icu, locale = 'ja-u-kr-latn-digit');
>
> Experiment 2:-
> SQL File : PG_Exp_2.sql
>
> Actual Output : PG_Exp_2.out
>
> Created 'COLLATION' : CREATE COLLATION test_coll (
> provider = icu, locale = 'ja-u-kr-digit-latn');
>
> 'SELECT' Queries : Same as 'Experiment 1'.
>
> Expectation : All digits should come before all alphabets.
>
> Need help in confirming why 'Experiment 1' is behaving as mentioned
> above -
> 1. If our expectation of 'Experiment 1' is wrong?

I am not sure.
but for the 'Experiment 1', I can use the following collation to get
the expected result you want, I think.
CREATE COLLATION x (provider = icu, locale = 'und-u-latn-digit');

------both two SELECTS, numeric first then alphabets
SELECT * FROM test_table ORDER BY value1 COLLATE x, value2 COLLATE x;
SELECT * FROM test_table ORDER BY value2 COLLATE x, value1 COLLATE x;

On Mon, Aug 25, 2025 at 8:59 PM jian he <jian.universality@gmail.com> wrote:

On Mon, Aug 25, 2025 at 3:52 PM Nishant Sharma
<nishant.sharma@enterprisedb.com> wrote:
>
>
> Experiment 1:-
> SQL File : PG_Exp_1.sql
>
> Actual Output : PG_Exp_1.out
>
> Created COLLATION : CREATE COLLATION test_coll (
> provider = icu, locale = 'ja-u-kr-latn-digit');
>
> Experiment 2:-
> SQL File : PG_Exp_2.sql
>
> Actual Output : PG_Exp_2.out
>
> Created 'COLLATION' : CREATE COLLATION test_coll (
> provider = icu, locale = 'ja-u-kr-digit-latn');
>
> 'SELECT' Queries : Same as 'Experiment 1'.
>
> Expectation : All digits should come before all alphabets.
>
> Need help in confirming why 'Experiment 1' is behaving as mentioned
> above -
> 1. If our expectation of 'Experiment 1' is wrong?

I am not sure.
but for the 'Experiment 1', I can use the following collation to get
the expected result you want, I think.
CREATE COLLATION x (provider = icu, locale = 'und-u-latn-digit');

------both two SELECTS, numeric first then alphabets
SELECT * FROM test_table ORDER BY value1 COLLATE x, value2 COLLATE x;
SELECT * FROM test_table ORDER BY value2 COLLATE x, value1 COLLATE x;

Thanks for your response!

Yeah, we can get the correct result with below create collation as well:

CREATE COLLATION test_coll (provider = icu,locale = 'ja',

rules = $$& a <*AbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ <*0-9$$);

 

That is by explicitly giving the priority rule. But we are curious to know

the root cause of 'Experiment 1' behaviour.

I will wait for any response for the same.

Regards,

Nishant.

Generally, there are two methods for performing collation-aware string
comparisons, and they are expected to produce equivalent results. For
libc collations, the first method is to use strcoll() or strcoll_l()
or similar to directly compared the strings, and the other is to use
strxfrm() or similar to convert the string to a normalize
representation which can then be compared using memcmp(). Equivalent
facilities exist for ICU; see collate_methods_icu and
collate_methods_icu_utf8 in pg_local_icu.c; one or the other of those
should be getting used here.

Even though these two methods are expected to produce equivalent
results, if there's a bug, they might not. That bug could either exist
in our code or it could exist in ICU; I suspect the latter is more
likely, but the former is certainly possible. What I think you want to
do is try to track down two specific strings where transforming them
via strnxfrm_icu() produces results that compare in one order using
memcmp(), but passing the same strings to strncoll_icu() or
strncoll_icu_utf8() -- whichever is appropriate -- produces a
different result. If you're able to find such strings, then you can
probably rephrase those examples in terms of whatever underlying
functions strncoll_icu() and strxfrm_icu() are calling and we can
maybe report the problem to the ICU maintainers. If no such strings
seem to exist, then there's probably a problem in the PostgreSQL code
someplace, and you should try to figure out why we're getting the
wrong answer despite ICU apparently doing the right thing.

--
Robert Haas
EDB: http://www.enterprisedb.com