> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to > be a bug with Virtual Tables. Queries using 'like' in the where > clause are not getting the like clause passed to BestIndex as a query > constraint. > Specifically: > > > - Simple query: select * from foo where name like 'a%'
The LIKE operator can be overridden by the application to mean anything the application wants - it is not compelled to follow standard SQL semantics. For that reason, virtual tables are unable to optimize using LIKE since they have no way of knowing what it will do. Works as designed. Not communicating the like constraint to virtual tables make it impossible for a virtual table to do query optimization to be done in that case. I realize like behavior can be overridden, but the resulting 'query' still needs to filter down to virtual table in some way to avoid full table scans. Otherwise any queries using 'like' against a virtual table of a substantial size become potentially unusable depending on 'time' required for a full table scan. Incidentally, this used to 'work' in a much older version of SQLite, though the semantics may not have been correct in all cases. Version 3.7.7.1, ended up transforming like to of pair of constraints in the "like 'a%'" case which were passed to BestIndex. Thanks, MikeN -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, March 05, 2015 10:46 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On 3/5/15, Mike Nicolino <mike.nicolino at centrify.com> wrote: > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to > be a bug with Virtual Tables. Queries using 'like' in the where > clause are not getting the like clause passed to BestIndex as a query > constraint. > Specifically: > > > - Simple query: select * from foo where name like 'a%' The LIKE operator can be overridden by the application to mean anything the application wants - it is not compelled to follow standard SQL semantics. For that reason, virtual tables are unable to optimize using LIKE since they have no way of knowing what it will do. Works as designed. > > - Break inside module BestIndex > > - SQLiteIndex.Inputs.Constraints has 0 length (no constraints) > > The above causes a full table scan of the virtual table for queries > using 'like', which is very bad for any virtual table of a substantial size. > Virtual tables need to be able to use 'like' clauses to restrict > result set size. > > Before I bug this issue, is anyone aware of it and have any workaround? > Currently, the only workaround I've got, is telling users don't use 'like' > in their queries (obviously not a good thing). > > Thanks, > MikeN > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
