On Tue, Aug 26, 2025 at 4:34 PM Nishant Sharma < [email protected]> wrote:
> 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. > Any help from experts on the confirmation about the ORDER BY with COLLATION behaviour mentioned in the first email/comment? Thanks in advance!
