Re: Problem in 'ORDER BY' of a column using a created collation?

On Tue, Aug 26, 2025 at 4:34 PM Nishant Sharma <
nishant(dot)sharma(at)enterprisedb(dot)com> wrote:

> On Mon, Aug 25, 2025 at 8:59 PM jian he <jian(dot)universality(at)gmail(dot)com>
> wrote:
>
>> On Mon, Aug 25, 2025 at 3:52 PM Nishant Sharma
>> <nishant(dot)sharma(at)enterprisedb(dot)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.
>

Any help from experts on the confirmation about the ORDER BY with
COLLATION behaviour mentioned in the first email/comment?

Thanks in advance!