The override of match() trick works pretty well for cases like this. I've overridden match in my virtual table implementation to allow me to pass arbitrary specialized queries directly to my virtual table modules for cases that I know the virtual table can do a better job that SQLite on that query. Downside is if you're exposing the SQL to users of course as using match in such a manner is non-standard.
MikeN -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Wednesday, June 17, 2015 1:23 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] What's the best way to pass function information to virtual table? Jerry wrote: > With xBestIndex and xFilter, we can pass the constraint information > (e.g., those from WHERE clause) to virtual table (through struct > sqlite3_index_info), so that we can locate the cursor to narrow the > search space. > However, it does not provide information about functions used in SQL > queries. > > For example, > >> SELECT MAX(key) from Table tab; > > The virtual table has no way to know the function MAX is called SQLite has a special optimization for this particular query, and rewrites it as "SELECT key FROM tab ORDER BY key DESC LIMIT 1". > The virtual table provides xFindFunction to override functions (maybe > this can be used to pass some information). For example, the full-text search module overrides the match() function; you could do something similar: SELECT key FROM tab WHERE tab MATCH 'max(key)' > But it seems only general functions can be override -- it has not > effect on aggregate functions. The virtual table interface does not allow access to all the internals of the query optimizer. When there is an aggregate function, you can filter the rows that will be given to it, but the actual aggregation is still done by SQLite. If you can compute aggregates more efficiently than SQLite, you could create a separate virtual table: SELECT max_key FROM tab_agg but this would not work for more complex queries. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
