BUG #5737: LIKE and ILIKE strange behaviour

From: "Radu Ilies" <ilies(dot)radu(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5737: LIKE and ILIKE strange behaviour
Date: 2010-10-31 19:28:37
Message-ID: 201010311928.o9VJSbKb079764@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: 5737
Logged by: Radu Ilies
Email address: ilies(dot)radu(at)gmail(dot)com
PostgreSQL version: 8.3.11
Operating system: NetBSD
Description: LIKE and ILIKE strange behaviour
Details:

Hello,

I have a table with:

id, char[25]
name, text

The first row is:

id='radu'
name='Radu Ilies'

The following queries does not find any result (fail):
SELECT * FROM my_table WHERE id LIKE 'radu'
SELECT * FROM my_table WHERE id ILIKE 'radu'

But the following find one row (successful):
SELECT * FROM my_table WHERE id = 'radu'

--------- I have tried to change the column ID:
from CHAR[25] to CHAR VARYING[25]
and !!! Surprise, it works !!!
after changing, these queries are successful (find one row):
SELECT * FROM my_table WHERE id LIKE 'radu'
SELECT * FROM my_table WHERE id ILIKE 'radu'
SELECT * FROM my_table WHERE id = 'radu'

They are also successful if I change ID field as TEXT.

Please notice that I am new to PostGreSQL, I came from a 10 years MySQL
experience, but in my opinion THIS IS A BUG since even if there is a
difference in storage from CHAR[25] to CHAR VARYING[25] to TEXT,
if this is successful (returns one row)
SELECT * FROM my_table WHERE id = 'radu'
also the followings should be successful:
SELECT * FROM my_table WHERE id LIKE 'radu'
SELECT * FROM my_table WHERE id ILIKE 'radu'
but they are not in the case of CHAR[25]

if I use on CHAR[25] as
SELECT * FROM my_table WHERE id LIKE 'radu%'
SELECT * FROM my_table WHERE id ILIKE 'radu%'
they come successful. But they should be without trailing % as the value of
the field is 'radu' and the query is successful (returns one row)
SELECT * FROM my_table WHERE id = 'radu'

Cheers,
Radu

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jon Nelson 2010-11-01 01:48:32 Re: [PERFORM] typoed column name, but postgres didn't grump
Previous Message Dimitri Fontaine 2010-10-31 15:32:48 Re: BUG #5736: 9.0.1 segmentation fault (sig11) during long-lived update