From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | steffen(at)city-map(dot)de, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: LIKE 'bla%' |
Date: | 2000-09-02 17:18:14 |
Message-ID: | 1388.967915094@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
pgsql-bugs(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-09-02 17:52:10 | Re: PG still fussy to compile on Solaris + GCC, may still need Sun ld |
Previous Message | pgsql-bugs | 2000-09-02 16:08:55 | PG still fussy to compile on Solaris + GCC, may still need Sun ld |