The fields param in match_bm25 isn't working as I expect. Not sure if this is on my end or the libs.
SQL:
CREATE TABLE IF NOT EXISTS table_fts AS
SELECT
CAST(col_1 AS VARCHAR) AS col_1,
CAST(col_2 AS VARCHAR) AS col_2,
CAST(col_3 AS VARCHAR) AS col_3
FROM table
PRAGMA create_fts_index(
'table_fts', 'col_1', col_1, col_2, col_3,
overwrite = 1,
ignore= '(\.|[^a-z0-9])+'
);
-- DOES NOT WORK AS EXPECTED (no results):
SELECT *,
fts_main_table_fts.match_bm25(
col_1,
'string', -- expected to match in col_3
fields := 'col_1, col_2, col_3',
) AS score
FROM table_fts
WHERE score IS NOT NULL
-- WORKS AS EXPECTED:
SELECT *,
fts_main_table_fts.match_bm25(
col_1,
'string', -- matches in col_3
fields := NULL,
) AS score
FROM table_fts
WHERE score IS NOT NULL
-- WORKS AS EXPECTED:
SELECT *,
fts_main_table_fts.match_bm25(
col_1,
'string', -- matches in col_3
fields := 'col_3',
) AS score
FROM table_fts
WHERE score IS NOT NULL
The fields param in match_bm25 isn't working as I expect. Not sure if this is on my end or the libs.
SQL:
CREATE TABLE IF NOT EXISTS table_fts AS
SELECT
CAST(col_1 AS VARCHAR) AS col_1,
CAST(col_2 AS VARCHAR) AS col_2,
CAST(col_3 AS VARCHAR) AS col_3
FROM table
PRAGMA create_fts_index(
'table_fts', 'col_1', col_1, col_2, col_3,
overwrite = 1,
ignore= '(\.|[^a-z0-9])+'
);
-- DOES NOT WORK AS EXPECTED (no results):
SELECT *,
fts_main_table_fts.match_bm25(
col_1,
'string', -- expected to match in col_3
fields := 'col_1, col_2, col_3',
) AS score
FROM table_fts
WHERE score IS NOT NULL
-- WORKS AS EXPECTED:
SELECT *,
fts_main_table_fts.match_bm25(
col_1,
'string', -- matches in col_3
fields := NULL,
) AS score
FROM table_fts
WHERE score IS NOT NULL
-- WORKS AS EXPECTED:
SELECT *,
fts_main_table_fts.match_bm25(
col_1,
'string', -- matches in col_3
fields := 'col_3',
) AS score
FROM table_fts
WHERE score IS NOT NULL