Table1 is Virtual Table with fts4 and TABLE2 is normal table.
Query1 (WORKS)
SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.id=TABLE2.id WHERE TABLE1 MATCH 'sometext' LIMIT %d,%d
Query 2 (DOES NOT WORK)
SELECT * FROM TABLE2 LEFT OUTER JOIN TABLE1 ON TABLE1.id=TABLE2.id WHERE TABLE1 MATCH 'sometext' LIMIT %d,%d
Error from Query2
android.database.sqlite.SQLiteException: unable to use function MATCH in the requested context (code 1)
From this it seems like FTS tabl need to be first on LEFT OUTER JOIN. Why this happening? Join happens first before applying WHERE clause. so if it was not working for table type mismatch i was assuming it would not work with query1 either. Anyone please explain this. what does happening internally? Also Any link to reference site would be appreciated explaining this.
MATCH works only on the FTS table itself, so it must be executed before the join. In the second query, the database looks up matching rows first (with an outer join, it has no choice in the join order), and that temporary result is no longer an FTS table.
When using FTS, it usually is a better idea to move the FTS search (or the other search) into a subquery:
SELECT *
FROM Table2 LEFT JOIN (SELECT *
FROM Table1
WHERE Table1 MATCH ...)
USING (id);