Skip site navigation (1) Skip section navigation (2)

Yet another problem with ILIKE and UTF-8

From: "Gergely Bor" <borg42(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Yet another problem with ILIKE and UTF-8
Date: 2007-10-25 12:40:46
Message-ID: 396c97310710250540i630de590l4185ed1f6512f8f2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
  Hello all,


I have a nasty-looking problem case. Shortly described as follows:

INSERT INTO mytable (id, value) VALUES (4242, 'úabcdú');
SELECT id FROM mytable WHERE value ILIKE '%abc%';

In environment A, the row of the ID just inserted is returned
correctly, but in environment B no rows are found. Uh! (Sadly
environment B is the productive environment... :/)

Notice the UTF-8 chars in the inserted sting and the _lack_ of UTF-8
chars in the searched string.

Environment A: Win2000, psql 8.2.4, lc_* is C, all encondings (client,
server, DB) are UTF-8.
Environment B: Debian lenny/sid ^[1], kernel version 2.6.20.1, glibc
2.6.1-5, psql 8.2.5, lc_* is hu_HU, all encondings (client, server,
DB) are UTF-8.

^[1]: I know :( but I'm not the sysadmin.

Notes:
 - The same query works if ILIKE is replaced with LIKE.
 - In environment B, ILIKE '%bcdú%' also returns 0 rows, but ILIKE
'%bcd%' works well. Quite strange.
 - I could not find the exact condition of the problem. A good lot of
queries work (provided the UTF-8 characters in the query exactly match
the DB value), but others like the above one do not.
 - From my prev. googling, I've learnt that ILIKE does not handle
UTF-8 strings searched _for_, and I've already implemented a
workaround for that, but obviously I can't do much for this case, when
the operand is a simple ASCII string.
 - From the first glance, this problem seems different from the ones
I've found during my searches (eg. links from
http://www.postgresql.org/docs/faqs.TODO.html,
http://svr5.postgresql.org/pgsql-bugs/2005-07/msg00157.php and
friends), but I'm not sure. If you think it's a new bug, please tell
it to me and I'll report it.

Well, any help is welcome, as our system is live and productive... :/


Best regards,
  Gergely BOR

Responses

pgsql-bugs by date

Next:From: Pierre-yves StrubDate: 2007-10-25 13:28:29
Subject: BUG #3696: FK integrity check bypassed using rules.
Previous:From: RogerDate: 2007-10-25 07:59:11
Subject: BUG #3695: Pgsql does not report non existing function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group