I'm making a note-taking app of Electron, using sqlite3(v3.31.0) as backend. Here is my creation statement for normal table Note:
create table `Note` (
`id` char(36),
`icon` text not null,
`title` text not null,
`body` text not null,
`parentId` char(36) not null,
`sortOrder` integer not null,
`userCreatedAt` datetime not null,
`userModifiedAt` datetime not null,
`childrenSortBy` text check (`childrenSortBy` in ('DEFAULT', 'TITLE', 'CREATED_AT', 'UPDATED_AT', 'CUSTOM')) not null,
`childrenSortDirect` text check (`childrenSortDirect` in ('ASC', 'DESC', 'DEFAULT')) not null,
foreign key(`parentId`) references `Note`(`id`),
primary key (`id`)
)
and here is my creation statement for virtual table:
CREATE VIRTUAL TABLE Note_FTS USING fts5(
title,
body,
content='Note'
);
CREATE TRIGGER Note_FTS_ai AFTER INSERT ON Note BEGIN
INSERT INTO Note_FTS(
rowid,
title,
body
) VALUES (
new.rowid,
new.title,
new.body
);
END;
CREATE TRIGGER Note_FTS_ad AFTER DELETE ON Note BEGIN
INSERT INTO Note_FTS(
Note_FTS,
rowid,
title,
body
) VALUES(
'delete',
old.rowid,
old.title,
old.body
);
END;
CREATE TRIGGER Note_FTS_au AFTER UPDATE ON Note BEGIN
INSERT INTO Note_FTS(
Note_FTS,
rowid,
title,
body
) VALUES(
'delete',
old.rowid,
old.title,
old.body
);
INSERT INTO Note_FTS(
rowid,
title,
body
) VALUES (
new.rowid,
new.title,
new.body
);
END;
After creation, I inserted a row into Note table, and Note_FTS was populated by trigger:

So far so good. But when I use a query:
SELECT *
FROM Note_FTS
WHERE Note_FTS MATCH 'aaa';
Sqlite return 0 row as result. Did I miss something or do anything wrong?