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
