"unable to use function highlight in the requested context" in group by

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

"unable to use function highlight in the requested context" in group by

Damian Adrian
Hello,

While using the FTS5 highlight() function in a group by query like this one:

CREATE VIRTUAL TABLE entries USING fts5(id UNINDEXED, content);

INSERT INTO entries
VALUES ('one', 'one'), ('two', 'another one' ), ('two', 'another two' );

SELECT
    group_concat(highlight(entries, 1, '>', '<'))
FROM entries
WHERE entries MATCH 'an*'
GROUP BY id;

I get "Error: unable to use function highlight in the requested context".

I have tried various sub-query combinations with the same result; the
FTS4 snippet() function results in a similar error; I am using SQLite
3.22.0. Full list:
https://gist.github.com/lemon24/49b0a999b26f7a40ba23d8d4fab4a828

My actual use case is a bit more involved (I'm trying to build a
json_group_array with the highlighted snippets of a single group), but
in the end I get the same error.

Is this by design? Am I doing something wrong? Is there another way of
obtaining a similar result?

Thank you,
Adrian
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: "unable to use function highlight in the requested context" in group by

Clemens Ladisch
Damian Adrian wrote:

> While using the FTS5 highlight() function in a group by query like this one:
>
> SELECT
>     group_concat(highlight(entries, 1, '>', '<'))
> FROM entries
> WHERE entries MATCH 'an*'
> GROUP BY id;
>
> I get "Error: unable to use function highlight in the requested context".
>
> I have tried various sub-query combinations with the same result;

Because SQLite does subquery flattening and ends up with the same query.

Try this:

SELECT group_concat(details)
FROM (
    SELECT
        id,
        highlight(entries, 1, '>', '<') as details
    FROM entries
    WHERE entries MATCH 'an*'
    LIMIT -1 OFFSET 0        -- rule 14 of https://www.sqlite.org/optoverview.html#subquery_flattening
)
GROUP BY id;


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: "unable to use function highlight in the requested context" in group by

Damian Adrian
In reply to this post by Damian Adrian
Thank you, that works!

I still think highlight() not working in this case may be a bug, though,
since it only operates on a single row and in theory shouldn't be affected
by the group by (but there may be limitations not obvious to me).

Thanks again,
Adrian
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: "unable to use function highlight in the requested context" in group by

Jake
Dan Kennedy explained why this limitation exists:

>On 27/2/62 05:47, Jake Thaw wrote:
>>This may not strictly be a bug, but currently (3.27.2) a vtab cannot overload scalar functions in aggregate queries.
>>
>>Adding a check for TK_AGG_COLUMN in sqlite3VtabOverloadFunction makes my use case function as expected.
>
>I think it has always been this way.
>
>The tricky case is when there is a GROUP BY with an expression list that the virtual table cannot optimize as an ORDER BY. In that case the virtual table cursor will be closed before the overloaded function is invoked, which breaks the implementation of most overloaded functions (including the built-in ones).
>
>Dan.

-Jake

On Sat, Jul 13, 2019 at 7:36 PM Damian Adrian <[hidden email]> wrote:

>
> Thank you, that works!
>
> I still think highlight() not working in this case may be a bug, though,
> since it only operates on a single row and in theory shouldn't be affected
> by the group by (but there may be limitations not obvious to me).
>
> Thanks again,
> Adrian
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users