Hmm, yes this will work. Though re-educating users writing their own queries to avoid like in this case will be an ongoing challenge. :) But I do understand that generic 'like' support for Virtual Tables given the ability to override would be very challenging to implement generically.
MikeN -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jay Kreibich Sent: Thursday, March 05, 2015 10:55 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On Mar 5, 2015, at 12:30 PM, 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: > "LIKE" is not a supported virtual table index constraint. See: http://www.sqlite.org/c3ref/c_index_constraint_eq.html As such, it cannot be passed to a virtual table implementation, and the constraint is handled by the SQLite engine above the virtual table implementation. Given that the LIKE expression is translated into an SQL function, which the application can override, it would be difficult for a virtual table to correctly implement a LIKE operation internally, while matching the exact functionality of the current LIKE function. Consider a statement like this: SELECT * FROM myVirtualTable AS vt WHERE returnThisRow_CustomFunction( vt.col1 ); If returnThisRow_CustomFunction() is a function returns a true or false based on... well, who knows what... there is no way for a virtual table implementation to understand the inter-workings of that function and pre-filter the rows. LIKE is no different. It should be noted that MATCH is a supported virtual table index constraint supported. Along with the virtual table xFindFunction() function allows a virtual table to implement a table specific filter function. This is how the FTS extensions implement searches. Consider providing a virtual table specific MATCH function, over-ride use on your table with xFindFunction(), and rewrite statements using MATCH rather than LIKE. See the FTS modules as examples. You might want to start here: https://www.sqlite.org/fts3.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users