SQLite Android Join operation on FTS4

Question

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.

Answer 1

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);

Answer 2

You can use join with FTS table.

I am using sqlite3 with FTS5 and this works for me

SELECT f.rowid as id, t.tag_name AS tag_name 
      FROM tags t JOIN tags_fts f ON f.rowid = t.id 
        WHERE f.tag_name MATCH ?
          AND t.deleted_at IS NULL 
        ORDER BY f.rank