SQLite FTS4 external content using view without column names

Question

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.

Answer 1

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.

Answer 2

The view needs a column named rowid. Id is not enough. Beware: The examples on the SQLite website for External Content Tables are misleading:

INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;

Using and id column works for a normal table, but not for a view. You need to use rowid instead:

INSERT INTO t3(docid, b, c) SELECT id rowid, b, c FROM t2;

Full console examples for linux with SQLite 3.11 based on the SQLite documentation examples.

Using a view does not work:

$ sqlite3 does_not_work.db;
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
sqlite> CREATE VIEW t2_v AS SELECT id, a, b, c FROM t2;
sqlite> CREATE VIRTUAL TABLE t3 USING fts4(content="t2_v", b, c);
sqlite> INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
sqlite> INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
sqlite> INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2_v;
sqlite> SELECT * FROM t3 WHERE t3 MATCH 'k';
|
sqlite> .exit

But setting a column alias of rowid for id when creating the view works:

sqlite3 works.db;
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
sqlite> CREATE VIEW t2_v AS SELECT id rowid, a, b, c FROM t2;
sqlite> CREATE VIRTUAL TABLE t3 USING fts4(content="t2_v", b, c);
sqlite> INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
sqlite> INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
sqlite> INSERT INTO t3(docid, b, c) SELECT rowid, b, c FROM t2_v;
sqlite> SELECT * FROM t3 WHERE t3 MATCH 'k';
i j|k l
sqlite> 

Using a normal table works though:

$ sqlite3 also_works.db;
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
sqlite> CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);
sqlite> INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
sqlite> INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
sqlite> INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;
sqlite> SELECT * FROM t3 WHERE t3 MATCH 'k';
i j|k l
sqlite>