Обсуждение: BUG #5737: LIKE and ILIKE strange behaviour

The following bug has been logged online:

Bug reference:      5737
Logged by:          Radu Ilies
Email address:      ilies.radu@gmail.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

On 10/31/10 12:28 PM, Radu Ilies wrote:
> 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'
>

'radu'::char[25] ==>  'radu_____________________'     (21 spaces).

the = equality coerces the 'radu'::text to 'radu
'::char[25] before doing the comparision.
LIKE/ILIKE doesn't think 'radu_____________________' is LIKE 'radu'

use TEXT for character types unless you have a very specific requirement
to limit them to a maximum length.