BUG #2554: ILIKE operator works incorrectly

From: "Jarosaw Bojar" <jarek(dot)bojar(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2554: ILIKE operator works incorrectly
Date: 2006-07-27 20:05:00
Message-ID: 200607272005.k6RK50Ar013165@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 2554
Logged by: Jarosaw Bojar
Email address: jarek(dot)bojar(at)gmail(dot)com
PostgreSQL version: 8.1.4
Operating system: i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 4.1.0
Description: ILIKE operator works incorrectly
Details:

ILIKE operator works incorrectly with UTF8 encoding and Polish characters.
Consider following SQL statements:

CREATE DATABASE test ENCODING='UTF8';
\c test
CREATE TABLE the_table (val VARCHAR(50));
INSERT INTO the_table (val) VALUES ('wiat');
INSERT INTO the_table (val) VALUES ('ka');
INSERT INTO the_table (val) VALUES ('ma');
INSERT INTO the_table (val) VALUES ('abc');
INSERT INTO the_table (val) VALUES ('ABC');

Without Polish characters ILIKE works correctly:
SELECT * FROM the_table WHERE val ilike 'abc';
val
-----
abc
ABC
(2 rows)

But with Polish characters it does not work correctly. Following queries
should give single row results, but they do not return any rows:

SELECT * FROM the_table WHERE val ilike 'wiat';
val
-----
(0 rows)

SELECT * FROM the_table WHERE val ilike 'ka';
val
-----
(0 rows)

SELECT * FROM the_table WHERE val ilike 'ma';
val
-----
(0 rows)

On the contrary functions like UPPER work correctly with Polish characters
and following queries produce correct results:

SELECT * FROM the_table WHERE UPPER(val) like UPPER('wiat');
val
-------
wiat
(1 row)

SELECT * FROM the_table WHERE UPPER(val) like UPPER('ma');
val
-----
ma
(1 row)

SELECT * FROM the_table WHERE UPPER(val) like UPPER('ka');
val
------
ka
(1 row)

The bug is also present in PostgreSQL 8.1.0 on Windows XP.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Roger Merritt 2006-07-27 23:27:20 Query returned unhandled type 16411
Previous Message Steven Adams 2006-07-27 19:30:01 BUG #2553: Outer join bug