Re: BUG #2592: ILIKE does not care about locales

On Tue, Aug 29, 2006 at 09:27:59AM -0400, Tom Lane wrote:
> Tino Schwarze <tino.schwarze@tisc.de> writes:
> > On Sun, Aug 27, 2006 at 12:58:00PM +0000, Robert Siemer wrote:
> >> I tried this with LC_COLLATE=C and the rest LC_...=es_ES.utf8
> >>
> >> dennisb from irc reported LC_everything=sv_SE.UTF-8 with version 8.1.0
> >> having the same problems.
>
> > I can confirm this with de_DE.utf8.
>
> ilike currently doesn't work for multibyte encodings (eg utf8).  This
> bug has been known for a long while, eg,
>
> http://archives.postgresql.org/pgsql-bugs/2005-10/msg00002.php
>
> but no one's stepped up to fix it.

The "use lower() for both strings" solution sounds reasonably simple to
me, but I'm not familiar with PgSQLs sources...

The funny thing is: It currently works, if you set the locale to de_DE
(non-UTF8)!

pg_controldata output:
LC_COLLATE:                           de_DE.utf8
LC_CTYPE:                             de_DE@euro

Output from test database:
test=# select * from test where test ilike '%ä%';
  test
---------
 äbcd
 Äbbcd
 bläbbcd
 BLÄbbcd
(4 rows)

test=# select * from test where test ilike '%Ä%';
  test
---------
 äbcd
 Äbbcd
 bläbbcd
 BLÄbbcd
(4 rows)

-> same result, both upper and lower ä match.

test=# select * from test where test ilike '%ö%';
 test
------
 ö
 Ö
(2 rows)

test=# select * from test where test ilike '%Ö%';
 test
------
 ö
 Ö
(2 rows)

test=# select * from test order by test desc;
  test
---------
 Ü
 ü
 Ö
 ö
 MÜLLER
 müller
 BLÄbbcd
 bläbbcd
 afbcd
 aebcd
 äbcd
 abcd
 Äbbcd
 aabcd
(14 rows)


Everything is fine with this weird locale setting. (All other
locales are set to C)...

Bye,

Tino.