Deletion of FTS5 entries causes "no such column"

[yspreen]

What did you do?

I created a FTS5 table and tried to delete an item from it.

Neither running delete where rowid = ? nor creating a delete trigger worked.

both result in the same error:

SQLite error 1: no such column: T.colname - while executingDELETE FROM "search" WHERE "rowid"=?`

colname is a placeholder and always shows the first column of my search table. I tried deleting that column, then the error shows up with the next column name. I tried with 1 and 2 columns in the search table.

I also tried creating the search table with raw SQL, same error. Neither the create table swift method nor raw SQL solve this issue.

Table creation works fine. Insertion works fine. Deletion causes the error.

Environment

GRDB flavor(s): SQLCipher GRDB version: GRDB.swift/SQLCipher (6.24.1) && SQLCipher (4.6.1) Installation method: CocoaPods Xcode version: 16.0 Swift version: 5 Platform(s) running GRDB: macOS macOS version running Xcode: sequoia

Demo Project

Let me know if this is needed, I can make one

[yspreen]

here's table creation:

                CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
                    title, 
                    body, 
                    content='other_table', 
                    content_rowid='id', 
                    tokenize='porter unicode61'
                );

CREATE TRIGGER search_delete AFTER DELETE ON other_table
FOR EACH ROW
BEGIN
        DELETE FROM search WHERE (rowid = old.id);
END;

[groue]

Hello @yspreen,

It looks like your question is more about SQLite and FTS5 than GRDB.

[yspreen]

it appears to be an issue of the sqlite runtime.

now I don't know if it's caused by macOS, sqlcipher, or grdb. all I know is that I get an error saying a column doesn't exist that's not part of my query ...

Running the same commands in a ubutu sqlite cli works fine

[groue]

now I don't know if it's caused by macOS, sqlcipher, or grdb.

GRDB does not alter your SQL statements, does not generate this error message.

Running the same commands in a ubutu sqlite cli works fine

You have that same cli on macOS: sqlite3. What does it give?

(I'm not sure this gives any information about SQLCipher, though)

[groue]

You can also compare your code with the triggers generated by the synchronize(withTable:) convenience method, inspired by the SQLite FTS5 documentation.

The AFTER DELETE trigger, in particular, is not the same as yours.

try DatabaseQueue().write { db in
    // Print SQL statements for close inspection
    db.trace { print($0) }

    try db.create(table: "other_table") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("title", .text)
        t.column("body", .text)
    }

    try db.create(virtualTable: "search", using: FTS5()) { t in
        t.tokenizer = .porter(wrapping: .unicode61())
        t.column("title")
        t.column("body")
        t.synchronize(withTable: "other_table") // <-
    }

    // No error on deletion
    try db.execute(sql: """
        INSERT INTO other_table (id, title, body) VALUES (1, 'Hello', 'World');
        DELETE FROM other_table WHERE id = 1;
        """)
}

CREATE TABLE "other_table" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "title" TEXT, "body" TEXT)

CREATE VIRTUAL TABLE "search" USING fts5(title, body, tokenize='''porter'' ''unicode61''', content='other_table', content_rowid='id')

CREATE TRIGGER "__search_ai" AFTER INSERT ON "other_table" BEGIN
    INSERT INTO "search"("rowid", "title", "body") VALUES (new."id", new."title", new."body");
END

CREATE TRIGGER "__search_ad" AFTER DELETE ON "other_table" BEGIN
    INSERT INTO "search"("search", "rowid", "title", "body") VALUES('delete', old."id", old."title", old."body");
END

CREATE TRIGGER "__search_au" AFTER UPDATE ON "other_table" BEGIN
    INSERT INTO "search"("search", "rowid", "title", "body") VALUES('delete', old."id", old."title", old."body");
    INSERT INTO "search"("rowid", "title", "body") VALUES (new."id", new."title", new."body");
END

INSERT INTO other_table (id, title, body) VALUES (1, 'Hello', 'World')
-- REPLACE INTO 'main'.'search_data'(id, block) VALUES(?,?)
-- TRIGGER __search_ai
-- INSERT INTO "search"("rowid", "title", "body") VALUES (new."id", new."title", new."body")
-- REPLACE INTO 'main'.'search_docsize' VALUES(?,?)
-- REPLACE INTO 'main'.'search_data'(id, block) VALUES(?,?)
-- PRAGMA 'main'.data_version
-- REPLACE INTO 'main'.'search_data'(id, block) VALUES(?,?)
-- INSERT INTO 'main'.'search_idx'(segid,term,pgno) VALUES(?,?,?)
-- REPLACE INTO 'main'.'search_data'(id, block) VALUES(?,?)

DELETE FROM other_table WHERE id = 1
-- TRIGGER __search_ad
-- INSERT INTO "search"("search", "rowid", "title", "body") VALUES('delete', old."id", old."title", old."body")
-- DELETE FROM 'main'.'search_docsize' WHERE id=?
-- REPLACE INTO 'main'.'search_data'(id, block) VALUES(?,?)
-- PRAGMA 'main'.data_version
-- REPLACE INTO 'main'.'search_data'(id, block) VALUES(?,?)
-- INSERT INTO 'main'.'search_idx'(segid,term,pgno) VALUES(?,?,?)
-- REPLACE INTO 'main'.'search_data'(id, block) VALUES(?,?)

[yspreen]

The following works on macos:

> rm test.sqlite3; sqlite3 test.sqlite3
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite> PRAGMA trusted_schema=1;
sqlite>
sqlite> CREATE TABLE IF NOT EXISTS other_table (
(x1...>     id INTEGER PRIMARY KEY AUTOINCREMENT,
(x1...>     body TEXT
(x1...> );
sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
(x1...>     body,
(x1...>     content='other_table',
(x1...>     content_rowid='id',
(x1...>     tokenize='porter unicode61'
(x1...> );
sqlite>
sqlite> CREATE TRIGGER search_delete AFTER DELETE ON other_table
   ...> FOR EACH ROW
   ...> BEGIN
   ...>     DELETE FROM search WHERE (old.id = rowid);
   ...> END;
sqlite>
sqlite> INSERT INTO other_table (body) VALUES ('test');
sqlite> INSERT INTO search (rowid, body) values (1, 'test');
sqlite>
sqlite> DELETE FROM other_table;
sqlite>

And the following works in GRDB:

try dbQueue.write { db in
            try db.execute(literal: """
                CREATE TABLE IF NOT EXISTS other_table (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    body TEXT
                );
                CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
                    body,
                    content='other_table',
                    content_rowid='id', 
                    tokenize='porter unicode61'
                );

                CREATE TRIGGER search_delete AFTER DELETE ON other_table
                FOR EACH ROW
                BEGIN
                    DELETE FROM search WHERE (old.id = rowid);
                END;

                INSERT INTO other_table (body) VALUES ('test');
                INSERT INTO search (rowid, body) values (1, 'test');

                DELETE FROM other_table;
            """)
        }

but as soon as I move from raw SQL to item.delete(db) after creating a trigger, it fails

[yspreen]

okay, I get it now. the content table has to have columns for the search table. my mistake, the errors didn't show up in the CLI I was using because I was constructing a basic example which did not have the same error pattern as my actual app.

In my app I wanted to search the table by a composite key that is not existent on the content table.

[groue]

I'm glad the issue is solved :) Happy GRDB!