I'm using FTS4
with sqlite 3.8.10
and I'm trying to set up a schema like so:
CREATE TABLE Test (name TEXT);
CREATE VIEW TestView as SELECT name FROM Test;
CREATE VIRTUAL TABLE TestFTS using FTS4(content="TestView", name);
In this toy example, I have a simple table, Test
and a view, TestView
, which is equivalent to that table. I create an FTS4
index, TestFTS
from the view.
Then I populate some data as follows:
INSERT INTO Test VALUES ('bob');
INSERT INTO Test VALUES ('bill');
INSERT INTO Test VALUES ('jane');
INSERT INTO TestFTS(TestFTS) VALUES ('rebuild');
So far so good. Now I want to query my data, for example:
SELECT name FROM TestFTS WHERE name MATCH "b*";
This should return two rows:
bob
bill
Instead it returns a single row and that row is blank. If I query all data from the TestFTS
table then I can see the values, but obviously it isn't much use if I can't do MATCH
type queries:
SELECT name FROM TestFTS;
bob
bill
jane
Any idea why SQLite
isn't returning the data, or even the correct number of rows, when using MATCH
queries on external content VIEW
s without column names?
====
UPDATE: I've tried using sqlite 3.13
with named column views and I'm seeing the same problem.
The documentation says:
When a users query on the FTS table requires a column value other than docid, FTS attempts to read the requested value from the corresponding column of the row in the content table with a rowid value equal to the current FTS docid.
So the view must include a unique column named rowid
that corresponds to the FTS table's docid
.