SQLite Forum

Unable to use FTS MATCH with OR condition
Thank you Max - the link to mail archive was very helpful. They had a similar problem with FTS3 queries back in 2010, and the suggested solution was to use sub selects, as building queries with virtual tables is finicky. E.g. the following works as expected and solves my immediate need:

FROM email e
    INNER JOIN email_address_from eaf ON eaf.email_id = e.id 
    INNER JOIN address_fts fa on fa.rowid = eaf.address_id
    INNER JOIN email_address_to eat ON eat.email_id = e.id 
    INNER JOIN address_fts ta on ta.rowid = eat.address_id
    fa.rowid IN (SELECT rowid FROM address_fts WHERE address_fts MATCH 'google')
    ta.rowid IN (SELECT rowid FROM address_fts WHERE address_fts MATCH 'google')

This solves my problem, as my queries are auto-generated and it's not hard to change the code to generate a slightly different expression.

Looks like I was under a mistaken assumptions that VIRTUAL TABLE behaves exactly like a normal table, with queries generated using the same rules etc. Maybe it's worth mentioning the difference somewhere in the documentation?