Re: case insensitive collation of Greek's sigma

Am 26.11.21 um 08:37 schrieb Jakub Jedelsky:
> Hello,
>
> during our tests of Postgres with ICU we found an issue with ILIKE of
> upper and lowercase sigma (Σ). The letter has two lowercase variants σ
> and ς (at the end of a word). I'm working with en_US and en-US-x-icu
> collations and results are a bit unexpected - they are inverted:
>
> postgres=# SELECT
> postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en_US",
> postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en_US"
> postgres-# ;
>  ?column? | ?column?
> ----------+----------
>  t        | f
> (1 row)
>
> postgres=# SELECT
> postgres-# 'ΣΣ' ILIKE 'σσ' COLLATE "en-US-x-icu",
> postgres-# 'ΣΣ' ILIKE 'σς' COLLATE "en-US-x-icu";
>  ?column? | ?column?
> ----------+----------
>  f        | t
> (1 row)
>
> I run those commands on the latest (14.1) official docker image.
>
> Is it possible to unify the behaviour?And which one is correct from
> the community point of view?
>
> If I could start, I think both results are wrong as both should return
> True. If I got it right, in the background there is a lower() function
> running to compare strings, which is not enough for such cases (until
> the left side isn't taken as a standalone word).
>
> Thanks,
>
> - jj

Have you seen the subtle intricacies in this example?

=> SELECT 'ΣΣ Μ' ILIKE 'σσ Μ' COLLATE "en_US" AS c0,
          'ΣΣ Μ' ILIKE 'σς Μ' COLLATE "en_US" AS c1,
          'ΣΣ Μ' ~*    'σσ Μ' COLLATE "el-GR-x-icu" AS c2,
          'ΣΣ Μ' ~*    'σς Μ' COLLATE "el-GR-x-icu" AS c3,
          'ΣΣ Μ' ILIKE 'σσ Μ' COLLATE "el-GR-x-icu" AS c4,
          'ΣΣ Μ' ILIKE 'σς Μ' COLLATE "el-GR-x-icu" AS c5,
          'ΣΣ Μ' ~*    'σσ Μ' COLLATE "en-US-x-icu" AS c6,
          'ΣΣ Μ' ~*    'σς Μ' COLLATE "en-US-x-icu" AS c7,
          'ΣΣ Μ' ILIKE 'σσ Μ' COLLATE "en-US-x-icu" AS c8,
          'ΣΣ Μ' ILIKE 'σς Μ' COLLATE "en-US-x-icu" AS c9;
 c0 | c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9
----+----+----+----+----+----+----+----+----+----
 t  | f  | t  | t  | f  | t  | t  | t  | f  | t
(1 row)

Obviously, the ILIKE operator is really strict regarding to the correct
letter at the end of the word. The regular expression operator works as
you expected.

Happy computing...
Frank