Thread: LIKE 'bla%'

Henrik Steffen (steffen@city-map.de) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
LIKE 'bla%'

Long Description
I just wanted to delete a record from a table, using

DELETE FROM MYTABLE WHERE NAME LIKE 'Ant%';

knowing that there existed only one record with 'Anton' as name.
However, the code above delivered 'DELETE 0'

Then I did this:
DELETE FROM MYTABLE WHERE NAME LIKE 'Anto%';
which gave 'DELETE 1'

Isn't this strange?


Sample Code
DELETE FROM MYTABLE WHERE NAME LIKE 'Ant%';
<=>
DELETE FROM MYTABLE WHERE NAME LIKE 'Anto%';


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> DELETE FROM MYTABLE WHERE NAME LIKE 'Ant%';
> knowing that there existed only one record with 'Anton' as name.
> However, the code above delivered 'DELETE 0'

> DELETE FROM MYTABLE WHERE NAME LIKE 'Anto%';
> which gave 'DELETE 1'

> Isn't this strange?

Yup.  What PG version are you using, and are you running it with a
non-English LOCALE setting?  Is there an index on the NAME column?

I suspect you are running into another variant of the problem we've
had for a long time concerning how to derive upper and lower index
boundes for a LIKE string.  In ASCII locale it's pretty easy:
    name >= 'Ant' AND name < 'Anu'
can be used to scan the index for all entries that might match the
given LIKE pattern.  But in non-ASCII locales with complicated collation
rules that method tends to fail.  See the pgsql-hackers mailing lists;
latest go-round was thread
    Sigh, LIKE indexing is *still* broken in foreign locales
in early June 2000.  At the moment I don't think we know a bulletproof
solution, other than not using indexes for LIKE, which won't make people
happy either ...

            regards, tom lane