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.
