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

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.