Why is this sqlite3 FTS5 contentless table not searchable?

Question

If I understand correctly, the purpose of a FTS5 contentless table is to only store indexing data, and not store the text itself, in order to save storage space.

Something seems wrong with the following contentless table, because is NOT searchable (query returns no rows) :

    drop table if exists f1;
    CREATE VIRTUAL TABLE f1 USING fts5(a, b, c, content='');
    insert into f1 (a, b, c) values ('zaz', 'pop', 'qasd');
    
    select ROWID from f1 where a like 'zaz';

> 0 rows retrieved in 34 ms

If I create the same table without content='' (to make it contentful), it works:

    drop table if exists f1;
    CREATE VIRTUAL TABLE f1 USING fts5(a, b, c);
    insert into f1 (a, b, c) values ('zaz', 'pop', 'qasd');
    
    select ROWID from f1 where a like 'zaz';

> 1 row affected in 7 ms

Why is my contentless table not searchable ?

score 0 · Answer 1 · edited May 09 '23 at 18:44

Your INSERT query is not supplying a rowid.

From SQLite Documentation:

A contentless FTS5 table is created by setting the "content" option to an empty string. For example:

CREATE VIRTUAL TABLE f1 USING fts5(a, b, c, content='');

Contentless FTS5 tables do not support UPDATE or DELETE statements, or INSERT statements that do not supply a non-NULL value for the rowid field.

...

Attempting to read any column value except the rowid from a contentless FTS5 table returns an SQL NULL value.

Why is this sqlite3 FTS5 contentless table not searchable?

1 Answers1