androidsqliteandroid-sqlitesqldelight

SQL Delight FTS5 MATCH gives no results

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?

Solution

  • 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.