On Mon, Aug 25, 2025 at 8:59 PM jian he <[email protected]> wrote:
> On Mon, Aug 25, 2025 at 3:52 PM Nishant Sharma > <[email protected]> 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.
