On 10/10/2012 10:01 PM, Ryan Johnson wrote:
On 10/10/2012 10:49 AM, Dan Kennedy wrote:
On 10/10/2012 08:14 PM, Hamish Allan wrote:
Short form question:
Working: SELECT a, userfunc(systemfunc) FROM t;
Working: SELECT a, sum(systemfunc) FROM t GROUP BY a;
Not working: SELECT a, sum(userfunc(systemfunc)) FROM t GROUP BY a;
Long form question:
I have a user-defined C function called "hits", loosely based on the
function "rankfunc" from the FTS3 example code, but returning (using
sqlite3_result_int) the total number of hits per row from the
matchinfo (code below).
As things are now, you can't use any of the FTS auxiliary functions
as arguments to an aggregate function.
The easiest workaround is probably to use a temp table to store the
unaggregated results of the FTS query.
What about a nested query?
SELECT r, sum(h) FROM (
SELECT rowid>>32 r, hits(matchinfo(text)) h
FROM text WHERE content MATCH 'a')
GROUP BY r;
Avoids the need to declare a temp table explicitly, and the optimizer
might even be able to avoid materializing the subquery.
... though if it turns out the above mechanical query rewrite works, I
would start to wonder why the original syntax can't be supported...
Actually that will cause a problem too, as the optimizer will
flatten the sub-query.
The error message will be something like "unable to use function
matchinfo() in the requested context".
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
