Re: [sqlite] Combining user-defined functions with aggregate functions
On 10/10/2012 10:09 PM, Hamish Allan wrote: On 10 October 2012 16:07, Dan Kennedywrote: On 10/10/2012 10:01 PM, Ryan Johnson wrote: On 10/10/2012 10:49 AM, Dan Kennedy wrote: The easiest workaround is probably to use a temp table to store the unaggregated results of the FTS query. What about a nested query? Actually that will cause a problem too, as the optimizer will flatten the sub-query. Is there any way to tell the optimiser to use a temporary table for the sub-select rather than flattening the sub-query? I don't think there is. It's this optimization causing the trouble: http://www.sqlite.org/optoverview.html#flattening You could change your query so that it doesn't qualify for that optimization I suppose. Easiest way would be to append "LIMIT -1 OFFSET 0" to the end of it. Of course, there is no guarantee that a future version of SQLite will not see through that, apply the optimization and hit the limitation. Dan. Thank you both for your replies. H ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Combining user-defined functions with aggregate functions
On 10/10/2012 11:07 AM, Dan Kennedy wrote: 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". So either (a) the nested query has different semantics than creating a temp table and selecting from it, or (b) the flattened version of the query has different semantics from the nested one? Sounds like a bug. Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Combining user-defined functions with aggregate functions
On 10 October 2012 16:07, Dan Kennedywrote: > > On 10/10/2012 10:01 PM, Ryan Johnson wrote: >> >> On 10/10/2012 10:49 AM, Dan Kennedy wrote: >>> >>> The easiest workaround is probably to use a temp table to store the >>> unaggregated results of the FTS query. >> >> What about a nested query? > > Actually that will cause a problem too, as the optimizer will > flatten the sub-query. Is there any way to tell the optimiser to use a temporary table for the sub-select rather than flattening the sub-query? Thank you both for your replies. H ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Combining user-defined functions with aggregate functions
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
Re: [sqlite] Combining user-defined functions with aggregate functions
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... Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Combining user-defined functions with aggregate functions
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. The function is working fine; I can query with: SELECT (rowid>> 32), hits(matchinfo(text)) FROM text WHERE content MATCH 'a'; and get output like: 1|21 1|6 1|6 1|5 1|8 1|10 1|8 1|16 1|48 (In case you're wondering what the shift is for, I'm using the high 32 bits of the rowid as a collection identifier, and the low 32 bits as a document identifier.) And for the query: SELECT (rowid>> 32), SUM(LENGTH(content)) FROM text WHERE content MATCH 'a' GROUP BY (rowid>> 32); I get output like: 1|6507 In other words, the user-defined function is working fine, and an aggregate query is working fine. But if I combine them, replacing the SQL with: SELECT (rowid>> 32), sum(hits(matchinfo(text))) FROM text WHERE content MATCH 'a' GROUP BY (rowid>> 32); I get a result code of 1, SQLITE_ERROR, "SQL error or missing database". What am I doing wrong? Thanks, Hamish // // Query code // const char *sql = "SELECT (rowid>> 32), hits(matchinfo(text)) FROM text WHERE content MATCH ?"; // works // const char *sql = "SELECT (rowid>> 32), sum(length(content)) FROM text WHERE content MATCH ? GROUP BY (rowid>> 32)"; // works const char *sql = "SELECT (rowid>> 32), sum(hits(matchinfo(text))) FROM text WHERE content MATCH ? GROUP BY (rowid>> 32)"; // doesn't work result = sqlite3_prepare_v2(db, sql, -1,, NULL); assert(result == SQLITE_OK); result = sqlite3_bind_text(stmt, 1, "a", -1, SQLITE_STATIC); assert(result == SQLITE_OK); do { result = sqlite3_step(stmt); if (result == SQLITE_ROW) { sqlite_int64 c0 = sqlite3_column_int64(stmt, 0); int c1 = sqlite3_column_int(stmt, 1); printf("%lld|%d", c0, c1); } } while (result == SQLITE_ROW); // // User-defined function code static void OLVSqliteHitsFunc(sqlite3_context *pCtx, int nVal, sqlite3_value **apVal) { if (nVal != 1) { sqlite3_result_error(pCtx, "Wrong number of arguments to PDFXHitsFunc", -1); return; } int hits = 0; unsigned int *blob = (unsigned int *)sqlite3_value_blob(apVal[0]); unsigned int numberOfPhrases = blob[0]; unsigned int numberOfColumns = blob[1]; int phrase; for (phrase = 0; phrase< numberOfPhrases; ++phrase) { unsigned int *phraseBlob =[2 + phrase * numberOfColumns * 3]; int column; for (column = 0; column< numberOfColumns; ++column) hits += phraseBlob[3 * column]; } sqlite3_result_int(pCtx, hits); } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Combining user-defined functions with aggregate functions
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). The function is working fine; I can query with: SELECT (rowid >> 32), hits(matchinfo(text)) FROM text WHERE content MATCH 'a'; and get output like: 1|21 1|6 1|6 1|5 1|8 1|10 1|8 1|16 1|48 (In case you're wondering what the shift is for, I'm using the high 32 bits of the rowid as a collection identifier, and the low 32 bits as a document identifier.) And for the query: SELECT (rowid >> 32), SUM(LENGTH(content)) FROM text WHERE content MATCH 'a' GROUP BY (rowid >> 32); I get output like: 1|6507 In other words, the user-defined function is working fine, and an aggregate query is working fine. But if I combine them, replacing the SQL with: SELECT (rowid >> 32), sum(hits(matchinfo(text))) FROM text WHERE content MATCH 'a' GROUP BY (rowid >> 32); I get a result code of 1, SQLITE_ERROR, "SQL error or missing database". What am I doing wrong? Thanks, Hamish // // Query code // const char *sql = "SELECT (rowid >> 32), hits(matchinfo(text)) FROM text WHERE content MATCH ?"; // works // const char *sql = "SELECT (rowid >> 32), sum(length(content)) FROM text WHERE content MATCH ? GROUP BY (rowid >> 32)"; // works const char *sql = "SELECT (rowid >> 32), sum(hits(matchinfo(text))) FROM text WHERE content MATCH ? GROUP BY (rowid >> 32)"; // doesn't work result = sqlite3_prepare_v2(db, sql, -1, , NULL); assert(result == SQLITE_OK); result = sqlite3_bind_text(stmt, 1, "a", -1, SQLITE_STATIC); assert(result == SQLITE_OK); do { result = sqlite3_step(stmt); if (result == SQLITE_ROW) { sqlite_int64 c0 = sqlite3_column_int64(stmt, 0); int c1 = sqlite3_column_int(stmt, 1); printf("%lld|%d", c0, c1); } } while (result == SQLITE_ROW); // // User-defined function code static void OLVSqliteHitsFunc(sqlite3_context *pCtx, int nVal, sqlite3_value **apVal) { if (nVal != 1) { sqlite3_result_error(pCtx, "Wrong number of arguments to PDFXHitsFunc", -1); return; } int hits = 0; unsigned int *blob = (unsigned int *)sqlite3_value_blob(apVal[0]); unsigned int numberOfPhrases = blob[0]; unsigned int numberOfColumns = blob[1]; int phrase; for (phrase = 0; phrase < numberOfPhrases; ++phrase) { unsigned int *phraseBlob = [2 + phrase * numberOfColumns * 3]; int column; for (column = 0; column < numberOfColumns; ++column) hits += phraseBlob[3 * column]; } sqlite3_result_int(pCtx, hits); } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users