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
