Re: ICU_LOCALE set database default icu collation but not working as intended.

2022-05-28 Thread Daniel Verite 
jian he wrote:

>   - dbicu3, ICU_LOCALE 'en-u-kr-latn-digit-kf-upper-kn-true'  seems
>   'kf-upper' not grouped strings beginning with character 'A' together?

You seem to expect that the sort algorithm takes characters
from left to right, and when it compares 'A' and 'a', it will
sort the string with the 'A' before, no matter what other
characters are in the rest of the string.

I don't think that's what kf-upper does. I think kf-upper kicks in
only for strings that are identical at the secondary level.
In your example, its effect is to make 'A 70' sort before
'a 70' . The other strings are unaffected.

>   - dbicu4, ICU_LOCALE 'en-u-kr-latn-digit-kn-true'  since upper/lower not
>   explicitly mentioned, and since the collation is deterministic, so
>   character  'A' should be grouped together first then do the numeric value

The deterministic property is only relevant when strings are compared equal
by ICU. Since your collations use the default strength setting (tertiary)
and the strings in your example are all different at this level,
the fact that the collation is deterministic does not play a role in
the results.

Besides, the TR35 doc says for "kn" (numeric ordering)

  "If set to on, any sequence of Decimal Digits (General_Category = Nd in
   the [UAX44]) is sorted at a primary level with its numeric value"

which means that the order of numbers (7, 11, 19, 70, 117) is "stronger"
(primary level) than the relative order of the 'a' and 'A' 
(case difference=secondary level) that precede them.
That's why these numbers drive the sort for these strings that are
otherwise identical at the primary level.


Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

Re: ICU_LOCALE set database default icu collation but not working as intended.

2022-05-26 Thread jian he 
Hi, here are some other trigger cases.

CREATE DATABASE dbicu3 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8'
ICU_LOCALE 'en-u-kr-latn-digit-kf-upper-kn-true' TEMPLATE 'template0';
CREATE DATABASE dbicu4 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8'
ICU_LOCALE 'en-u-kr-latn-digit-kn-true' TEMPLATE 'template0';
--mistake
CREATE DATABASE dbicu5 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8'
ICU_LOCALE 'en-u-kr-latn-digit-kr-upper' TEMPLATE 'template0';
CREATE DATABASE dbicu6 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8'
ICU_LOCALE 'en-u-kr-latn-digit-kf-upper' TEMPLATE 'template0';

--same script applies to dbicu3, dbicu4, dbicu5, dbicu6.
begin;
CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper');
CREATE TABLE icu(def text, en text COLLATE "en_US", upfirst text COLLATE
upperfirst, test_kr text);
INSERT INTO icu VALUES ('a', 'a', 'a', '1 a'), ('b','b','b', 'A 11'),
('A','A','A','A 19'), ('B','B','B', '8 p');
INSERT INTO icu VALUES ('a', 'a', 'a', 'a 7'),('a', 'a', 'a', 'a 117');
INSERT INTO icu VALUES ('a', 'a', 'a', 'a 70'), ('a', 'a', 'a', 'A 70');
INSERT INTO icu VALUES ('a', 'a', 'a', 'Π1');
commit ;
---
localhost:5433 admin@dbicu3=# SELECT test_kr FROM icu  ORDER BY test_kr ;

 test_kr
-
 a 7
 A 11
 A 19
 A 70
 a 70
 a 117
 Π1
 1 a
 8 p
(9 rows)
--
localhost:5433 admin@dbicu4=# SELECT test_kr FROM icu  ORDER BY test_kr ;
 test_kr
-
 a 7
 A 11
 A 19
 a 70
 A 70
 a 117
 Π1
 1 a
 8 p
(9 rows)

localhost:5433 admin@dbicu6=#  SELECT test_kr FROM icu  ORDER BY test_kr ;
 test_kr
-
 A 11
 a 117
 A 19
 a 7
 A 70
 a 70
 Π1
 1 a
 8 p
(9 rows)
-

   - dbicu3, ICU_LOCALE 'en-u-kr-latn-digit-kf-upper-kn-true'  seems
   'kf-upper' not grouped strings beginning with character 'A' together?


   - dbicu4, ICU_LOCALE 'en-u-kr-latn-digit-kn-true'  since upper/lower not
   explicitly mentioned, and since the collation is deterministic, so
   character  'A' should be grouped together first then do the numeric value
   comparison.


   -  dbicu6, ICU_LOCALE 'en-u-kr-latn-digit-kf-upper' , from the
result, *kr-latn-digit
   *is working as intended. But *kf-upper *seems not working.

maybe this link(
https://www.unicode.org/reports/tr35/tr35-collation.html#314-case-parameters
) can help.

Can I specify as many key-value settings options (
https://www.unicode.org/reports/tr35/tr35-collation.html#table-collation-settings)
as I want in ICU_LOCALE while I create a new database?


On Fri, May 27, 2022 at 1:44 AM Daniel Verite 
wrote:

> jian he wrote:
>
> > CREATE
> > DATABASE dbicu1 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8' ICU_LOCALE
> > 'en-u-kf-upper' TEMPLATE 'template0';
> > CREATE DATABASE dbicu2 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8'
> ICU_LOCALE
> > 'en-u-kr-latn-digit' TEMPLATE 'template0';
> > [...]
> > I am not sure this is my personal misunderstanding.
> > In the above examples, the first character of column *test_kr*
> > is so different that the comparison is based on the first letter.
> > If the first letter is the same then compute the second letter..
> > So for whatever collation, I should expect 'A 19' to be adjacent with 'A
> > 11'?
>
> The query "SELECT test_kr FROM icu  ORDER BY def;"
> does not order by test_kr, so the contents of test_kr have no bearing
> on the order of the results.
>
> If you order by test_kr, the results look like what you're expecting:
>
> dbicu1=# SELECT test_kr,def FROM icu  ORDER BY test_kr;
>  test_kr | def
> -+-
>  1 a | a
>  8 p | B
>  A 11| b
>  A 19| A
>  a 7 | a
>  Π1 | a
>
> dbicu2=# SELECT test_kr,def FROM icu  ORDER BY test_kr ;
>  test_kr | def
> -+-
>  A 11| b
>  A 19| A
>  a 7 | a
>  Π1 | a
>  1 a | a
>  8 p | B
>
>
>
> Best regards,
> --
> Daniel Vérité
> https://postgresql.verite.pro/
> Twitter: @DanielVerite
>


-- 
 I recommend David Deutsch's <>

  Jian

Re: ICU_LOCALE set database default icu collation but not working as intended.

2022-05-26 Thread Daniel Verite 
jian he wrote:

> CREATE
> DATABASE dbicu1 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8' ICU_LOCALE
> 'en-u-kf-upper' TEMPLATE 'template0';
> CREATE DATABASE dbicu2 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8' ICU_LOCALE
> 'en-u-kr-latn-digit' TEMPLATE 'template0';
> [...]
> I am not sure this is my personal misunderstanding.
> In the above examples, the first character of column *test_kr*
> is so different that the comparison is based on the first letter.
> If the first letter is the same then compute the second letter..
> So for whatever collation, I should expect 'A 19' to be adjacent with 'A
> 11'?

The query "SELECT test_kr FROM icu  ORDER BY def;"
does not order by test_kr, so the contents of test_kr have no bearing
on the order of the results.

If you order by test_kr, the results look like what you're expecting:

dbicu1=# SELECT test_kr,def FROM icu  ORDER BY test_kr;
 test_kr | def 
-+-
 1 a | a
 8 p | B
 A 11| b
 A 19| A
 a 7 | a
 Π1 | a

dbicu2=# SELECT test_kr,def FROM icu  ORDER BY test_kr ;
 test_kr | def 
-+-
 A 11| b
 A 19| A
 a 7 | a
 Π1 | a
 1 a | a
 8 p | B



Best regards,
-- 
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

ICU_LOCALE set database default icu collation but not working as intended.

2022-05-26 Thread jian he 
Base on this thread:
https://www.postgresql.org/message-id/20220305083830.lpz3k3yku5lmm5xs%40jrouhaud
ordering reference:
https://unicode-org.github.io/cldr-staging/charts/latest/collation/en_US_POSIX.html

CREATE
DATABASE dbicu1 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8' ICU_LOCALE
'en-u-kf-upper' TEMPLATE 'template0';
CREATE DATABASE dbicu2 LOCALE_PROVIDER icu LOCALE 'en_US.UTF-8' ICU_LOCALE
'en-u-kr-latn-digit' TEMPLATE 'template0';
--same script apply to dbicu1 dbicu2
BEGIN;
CREATE COLLATION upperfirst (
provider = icu,
locale = 'en-u-kf-upper'
);
CREATE TABLE icu (
def text,
en text COLLATE "en_US",
upfirst text COLLATE upperfirst,
test_kr text
);
INSERT INTO icu
VALUES ('a', 'a', 'a', '1 a'), ('b', 'b', 'b', 'A 11'), ('A', 'A', 'A',
'A 19'), ('B', 'B', 'B', '8 p');
INSERT INTO icu
VALUES ('a', 'a', 'a', 'a 7');
INSERT INTO icu
VALUES ('a', 'a', 'a', 'Π1');
COMMIT;
---
--dbicu1
SELECT def AS def FROM icu ORDER BY def; --since only character. all works
fine.
SELECT test_kr FROM icu  ORDER BY def;
/*
  test_kr
-
 A 19
 1 a
 a 7
 Π1
 8 p
 A 11
 */


--dbicu2
SELECT def AS def FROM icu ORDER BY def; --since only character. all works
fine.
SELECT test_kr FROM icu  ORDER BY def;
/*
 test_kr
-
 1 a
 a 7
 Π1
 A 19
 A 11
 8 p
(6 rows)
*/

Since dbicu1 and dbicu2 set the default collation then
In dbicu1, I should expect the ordering:
 number >> Upper case alphabet letter  >> lower case alphabet letter>>
character Π(U+0153)

In dbicu2, I should expect the ordering:
 lower case  alphabet letter >> Upper case alphabet letter >> number >>
character Π(U+0153)

As you can see one letter works fine for dbicu1, dbicu2. However, it does
not work on more characters.
Or The result is correct, but something I misunderstood?

I am not sure this is my personal misunderstanding.
In the above examples, the first character of column *test_kr*
is so different that the comparison is based on the first letter.
If the first letter is the same then compute the second letter..
So for whatever collation, I should expect 'A 19' to be adjacent with 'A
11'?



-- 
 I recommend David Deutsch's <>

  Jian