Thread

1. LIKE 'bla%'

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-09-02T09:18:18Z

   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
   
   
   

2. Re: LIKE 'bla%'

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-09-02T17:18:14Z

   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