SQLite FTS5 Match is returning nothing

Question

I have a SQLite3 table:

CREATE TABLE "test" (
    "title" TEXT,
    "shortdesc" TEXT,
    "longdesc"  TEXT,
    "id"    TEXT,
    PRIMARY KEY("id")
);

I insert anything in it:

INSERT INTO test (id, title, shortdesc, longdesc) VALUES ("abc", "hello world", "this is a hello world", "a nice hello world article about hello worlds")

Then I create a FTS5 virtual table:

CREATE VIRTUAL TABLE IF NOT EXISTS test_fts USING fts5 (
        id,
        title,
        shortdesc,
        longdesc,
        content=test
);

So I check data in virtual table:

Everything seems fine... Now I try to use MATCH to find the article:

SELECT * FROM test_fts WHERE test_fts MATCH 'hello'

...and I get nothing in result. Obviously this database I showed is just an example, same thing happens with actual database. I tried on different computers (and different clients), I also checked if FTS5 is enabled and compiled in it with PRAGMA compile_options and ENABLE_FTS5 is there, meaning it's enabled. Same thing happens with FTS3 and 4.

So what am I missing? SQLite version is 3.36.0.

score 4 · Accepted Answer · answered Nov 15 '21 at 21:57

The FTS5 tables still need to be populated. This includes external content cases. From the documentation:

It is still the responsibility of the user to ensure that the contents of an external content FTS5 table are kept up to date with the content table. One way to do this is with triggers. For example:

-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
 INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
 INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
 INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
 INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;

So, you could either create the table and triggers before populating the main table, or in your test case, after you create the FTS table you can run a query like this to populate the FTS table for the first time:

INSERT INTO test_fts SELECT * FROM test;

Then your query will work as expected.

Thank you, that worked. What I don't understand though is that why when I query everything from it via `SELECT * FROM test_fts` it actually returns everything even if I didn't populate it with data. — ifbamoq, Nov 16 '21 at 09:33
@ifbamoq It looks like it automatically populates the main table when you create it, but not some of the auxiliary tables like `*_data` that it needs to actually find stuff. Then when you do an INSERT, it populates those tables (even though no new entries are actually inserted to the table you said to insert into). Really odd (and as far as I can tell, undocumented) default behavior if it's not a bug. — Soren Bjornstad, Jun 25 '22 at 20:33

SQLite FTS5 Match is returning nothing

1 Answers1