On Fri, Nov 26, 2010 at 6:23 AM, Spiros Ioannou <siv...@image.ece.ntua.gr>wrote:
> Hello, I recently updated from 3.4.2 to 3.7.3 and the glob and like
> operators are now not using the indexes.
> My table "dict" has a column named "word" without defined type, containing
> utf-8 words. The index is:
> CREATE INDEX wordidx on dicts (word);
>
> explain query plan SELECT * from dicts where word like 'xxxx%' limit
> 1;
> 0|0|TABLE dicts
>
>
> sqlite> explain query plan SELECT * from dicts where word glob 'xxxx*'
> limit 1;
> 0|0|TABLE dicts
>
> BUT:
> sqlite> explain query plan SELECT * from dicts where word > 'xxxx' limit
> 1;
> 0|0|TABLE dicts WITH INDEX wordidx
>
> using collate binary before the "limit" in glob or like doesn't help. using
> PRAGMA case_sensitive_like = 0/1 didn't help either.
> Is this a bug?
>
This is not a bug but a bug fix. Version 3.4.2 was wrong. Version 3.7.3 is
right. The LIKE/GLOB indexing optimization can only be (safely) applied to
a column that has TEXT affinity. Using the LIKE/GLOB indexing optimization
on any other column can result in an incorrect answer. See
http://www.sqlite.org/cvstrac/tktview?tn=3901
Your fix is to declare column "word" to have type TEXT.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
