Hi, MikeN.
Assuming I want to compute the average value of all keys, how to write the
query for this using match?
For example, if I write SQL in this way
> SELECT avg(key) From table WHERE key MATCH('avg');
with MATCH info, the virtual table is able to know the query is looking for
average value for the keys.
Now, assume the virtual table is able to compute the average faster than
SQLite, after I get the average value, how can I return the result
immediately without SQLite going through the aggregator avg()?
What are your queries like when you "pass arbitrary specialized queries
directly to my virtual table"?
Thanks,
-C.Lin
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
>
