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, &stmt, 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 = &blob[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