Thanks, MikeN. I will consider MATCH for now, although it is not standard. 2015-06-17 8:48 GMT-07:00 Mike Nicolino <mike.nicolino at centrify.com>:
> 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-bounces at 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
