I have the following table:

CREATE TABLE IF NOT EXISTS "note" (
    "noteid"    INTEGER NOT NULL,
    "title" TEXT NOT NULL,
    "description"   TEXT NOT NULL,
    PRIMARY KEY("noteid")
);

Then I execute:

CREATE VIRTUAL TABLE IF NOT EXISTS NoteFts USING fts5(title, description, content='note', content_rowid='noteid');
CREATE TRIGGER IF NOT EXISTS note_ai AFTER INSERT ON note BEGIN INSERT INTO NoteFts(rowid, title, description) VALUES (new.noteid, new.title, new.description); END;
CREATE TRIGGER IF NOT EXISTS note_ad AFTER DELETE ON note BEGIN INSERT INTO NoteFts(NoteFts, rowid, title, description) VALUES ('delete', old.noteid, old.title, old.description); END;
CREATE TRIGGER IF NOT EXISTS note_au AFTER UPDATE ON note BEGIN INSERT INTO NoteFts(NoteFts, rowid, title, description) VALUES ('delete', old.noteid, old.title, old.description); INSERT INTO NoteFts(rowid, title, description) VALUES (new.noteid, new.title, new.description); END;

Then:

INSERT INTO note(title, description) VALUES ("note1", "desc1");
INSERT INTO note(title, description) VALUES ("note2", "desc2");

On DB Browser

SELECT * FROM note WHERE noteid IN (SELECT rowid FROM NoteFts WHERE NoteFts MATCH 'note1';

works perfectly. On my Android application the MATCH (or =, or NoteFts('note1')) doesn't work for some reason. I'm pretty sure the code is executed in the same order as given above, but here are the specifics:

1. I'm using 2.0.0-alpha03 version of SQL Delight;
2. the content table ("notes") is created in an .sq file using SQL Delight;
3. the virtual table and the triggers are created in SqlDriver.execute(null, "the SQL that creates the FTS5 table and triggers", 0) after the SqlDriver is created but before the content table is populated.
4. Through testing I've found that the FTS5 table is being created so that's not the problem. Trying to query it as a normal table works fine.
5. I even tried INSERT INTO NoteFts(title, description) SELECT title, description FROM note; just in case, in different places throughout the code and again - MATCH is not returning any results.

Does anyone have any idea why it might not work?

CodePudding user response：

I am having a very similar issue at the moment. One reason the MATCH or its equivalent with = is not returning is that the FTS index appears to be corrupt. Somehow it happens even on a "fresh" index table, even when done in DBBrowser.

• Give a look at FTS5's documentation on the "6. Special INSERT Commands" section, the integrity-check and rebuild commands are explained there, after a rebuild the index table should start returning results.

• Bear in mind that external content index tables only store the indexing data, any queries for the indexed columns on them are passed through to the external content table, hence the non-empty results when doing plain queries against the index table.