[SOLVED] SQLite FTS4 external content using view without column names

SQLite FTS4 external content using view without column names

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 VIEWs without column names?

====

UPDATE: I've tried using sqlite 3.13 with named column views and I'm seeing the same problem.

Solution

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.