[sqlite] Combining user-defined functions with aggregate functions

[Hamish Allan]

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