Re: [sqlite] Combining user-defined functions with aggregate functions

[Dan Kennedy]

On 10/10/2012 10:09 PM, Hamish Allan wrote:

On 10 October 2012 16:07, Dan Kennedy  wrote:


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

[Ryan Johnson]

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

[Hamish Allan]

On 10 October 2012 16:07, Dan Kennedy  wrote:
>
> 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

[Dan Kennedy]

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

[Ryan Johnson]

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

[Dan Kennedy]

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

[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, , 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