SQLite FTS5: "unable to use function highlight in the requested context"; https://github.com/lemon24/reader/issues/122

sqlite-fts5-highlight-group-by.sql

-- fts5

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

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

-- don't work

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

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

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

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

-- work; see:
-- https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg115821.html

SELECT group_concat(details)
FROM (
    SELECT
        id,
        highlight(entries, 1, '>', '<') as details
    FROM entries
    WHERE entries MATCH 'an*'
    LIMIT -1 OFFSET 0
)
GROUP BY id;

WITH entries_details AS (
    SELECT
        id,
        highlight(entries, 1, '>', '<') as details
    FROM entries
    WHERE entries MATCH 'an*'
    LIMIT -1 OFFSET 0
)
SELECT group_concat(details)
FROM entries_details
GROUP BY id;

-- fts4

CREATE VIRTUAL TABLE entries4 USING fts4(
    id,
    content,
    notindexed=id
);

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

SELECT
    group_concat(snippet(entries4, '>', '<', '...', 1))
FROM entries4
WHERE entries4 MATCH 'an*'
GROUP BY id;

-- Output:
--
-- $ sqlite3 ':memory:' '.read sqlite-fts5-highlight-group-by.sql'
-- Error: near line 17: unable to use function highlight in the requested context
-- Error: near line 23: unable to use function highlight in the requested context
-- Error: near line 33: unable to use function highlight in the requested context
-- Error: near line 44: unable to use function highlight in the requested context
-- >another< one,>another< two
-- >another< one,>another< two
-- Error: near line 92: unable to use function snippet in the requested context
--
-- $ sqlite3 -version
-- 3.22.0 2018-01-22 18:45:57 0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1

This solved my issue. Thank you very much for posting. I found that even just LIMIT -1 seems to work, I didn’t even need the OFFSET 0.

This works for FTS5. Many thanks.
My query

SELECT t_col1, t_col2H, max(t_col3) AS t_col3 FROM 
 (SELECT t.col1 AS t_col1, highlight(table1_fts, 0, '<b>', '</b>') AS t_col2H, t.col3 AS t_col3 
  FROM table1 t JOIN table1_fts tf ON t.id = tf.rowid 
  WHERE t.col1 IN (1,3,9,10,12,15) AND table1_fts MATCH 'set*' LIMIT -1) 
GROUP BY t_col1;