SQLite: FTS5 'delete'd row still appears in MATCH results-CodePudding

I have a table:

CREATE TABLE Tests (col1 INTEGER, col2 INTEGER, col3 INTEGER, col4 INTEGER, col5 INTEGER);

with values:

INSERT INTO Tests (col1, col2, col3, col4, col5)
VALUES (1, 2, 3, 4, 5);
INSERT INTO Tests (col1, col2, col3, col4, col5)
VALUES (6, 7, 8, 9, 10);
INSERT INTO Tests (col1, col2, col3, col4, col5)
VALUES (11, 12, 13, 14, 15);
INSERT INTO Tests (col1, col2, col3, col4, col5)
VALUES (16, 17, 18, 19, 20);

I created an FTS5 table for it:

CREATE VIRTUAL TABLE TestFTS5 USING fts5(col1,
                                         col2,
                                         col3,
                                         tokenize = "ascii separators ' '",
                                         content='');

INSERT INTO TestFTS5 (rowid, col1, col2, col3)
SELECT DISTINCT rowid, col1,
                       col2,
                       col3
FROM Tests;

Without any delete statements, the following query:

SELECT COUNT(1)
FROM TestFTS5
WHERE TestFTS5 MATCH '1* OR 7';

returns 4.
However, even after deleteing a row from the FTS5 table:

INSERT INTO TestFTS5 (TestFTS5, rowid, col1, col2, col3)
VALUES ('delete', 1, NULL, NULL, NULL);

the SELECT query still returns 4.
I tried deleting from both the main table and the FTS5 table:

DELETE
FROM Tests
WHERE rowid = 4;

INSERT INTO TestFTS5 (TestFTS5, rowid, col1, col2, col3)
VALUES ('delete', 4, NULL, NULL, NULL);

but the SELECT query still returns 4.
Thinking it had something to do with the transaction not being committed, I saved the changes but the SELECT query still returned 4. I did notice that after every 'delete' INSERT the TestFTS5_docsize table was losing a row corresponding to the "deleted" row ID. Am I doing something wrong here? I think I'm following what the documentation for 'delete' says to a T, but the deleted row is still included in the results.

CodePudding user response：

The content='' option creates a contentless table, the columns values are integrated in the index but not stored. This is why they are always null :

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

When deleting rows you have to provide again the values, so that the index can be updated :

INSERT INTO TestFTS5 (TestFTS5, rowid, col1, col2, col3)
VALUES ('delete', 1, 1, 2, 3);

If you are actually storing the values in another table, you can use it as external content, with content='Tests'

You can then use the standard delete

delete from TestFTS5 where rowid = 2

SQLite will use the row in Tests (with the same rowid) to retrieve the needed values.

This means that if you also delete the row from external table, you must do it after deleting the row in the FTS table.

In fact you are responsible for maintaining the both tables in sync.