I recall you must still enumerate the column names of the fields you wish to link to the contentless table when creating the virtual table.
If I remember correctly, failing to do so will result in 'no such column' errors. Last time I looked at the docs I got caught with the same gotcha, the docs show an example query of how the contentless content is loaded but (possibly?) don't make it 100% clear whether you need to specify those columns in the vtable definition in order to allow them to be returned in the result rows. -P On Sun, 1 Jul 2018, 12:07 PM Dan Kennedy, <[email protected]> wrote: > On 06/30/2018 10:24 PM, Dudu Markovitz wrote: > > Hi > > > > according to the documentation - > > 6.2.2. External Content FTS4 Tables > > > > An "external content" FTS4 table is similar to a contentless table, > except > > that if evaluation of a query requires the value of a column other than > > docid, FTS4 attempts to retrieve that value from a table (or view, or > > virtual table) nominated by the user (hereafter referred to as the > "content > > table"). > > > > However I see no example for a query on the fts table that implicitly > > retrieves values from the content table, nor could I find any in other > > place. > > All my attempts to write such a query ended up with "Error: no such > > column". > > Am I missing something or is it a problem in the documentation? > > I don't think I understand the question. What do you want it to do? > > The SELECT query in the second block of code in that section: > > https://sqlite.org/fts3.html#_external_content_fts4_tables_ > > is: > > SELECT * FROM t3 WHERE t3 MATCH 'k' > > t3 is the FTS4 table. The query does a lookup on the FTS4 index to find > matches for token 'k', then automatically retrieves values from the > content table (t2) for each matched row. The first instance of the > SELECT in the block of code shows everything working as expected, the > subsequent two show the counter-intuitive effects of allowing the FTS > index to get out of sync with the content table. > > Dan. > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
