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
