Jerry wrote: > 2015-06-17 1:23 GMT-07:00 Clemens Ladisch <clemens at ladisch.de>: >> Jerry wrote: >>> 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. > > Can you tell more on this? According to my understanding, the constraints > appeared in WHERE clause will be passed to virtual table (through > xBestIndex() ), then xFilter() can use the information to narrow the search > space. However, as for functions, virtual table does not know which > function is called, so the filter is not able to filter rows based on the > function.
Yes; you get only constraints of a form like "MyColumn = some_value", and you don't get to know what that value is, or where it came from. >> 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. > > I think using separate tables might not be a good solution if the table > keeps updating -- there might be consistency issues. A _virtual_ table does not necessarily imply that the data is actually stored in some real table; the values could be computed dynamically. But a separate virtual table is useful only if the data has a different structure from the data in the original table. Otherwise, using MATCH appears to be a better solution. Regards, Clemens
