1

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.

Phantômaxx
  • 37,901
  • 21
  • 84
  • 115
varuog
  • 3,031
  • 5
  • 28
  • 56
  • Why are you trying to join TABLE2 on TABLE2 ? Also, MATCH is for matching a column, not matching a table. – BladeCoder May 24 '17 at 21:44
  • It was a typo. I edited it. Sorry. Also as per sqlite doc table can be specified for left hand side. In that case it will try to match from all columns. – varuog May 24 '17 at 21:48

2 Answers2

4

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);
CL.
  • 173,858
  • 17
  • 217
  • 259
  • So is it right that in query1 first FTS table with matches evaluated first and then join happens with table2? – varuog May 25 '17 at 09:16
  • Yes, that is the order that the query optimizer happens to use. – CL. May 25 '17 at 09:18
  • is it only with sqlite? as far i can remember with mysql it works different than that. – varuog May 25 '17 at 09:20
  • So you are running MySQL with FTS4 on Android? ;-) – CL. May 25 '17 at 09:22
  • no i was talking about joining a fulltext index enabled MyIsam table with InnoDb table. what i meant was is it default behavior of sql specification or it is specific with SqLite. – varuog May 25 '17 at 09:27
  • The SQL specification does not say anything about vendor-specific extensions. – CL. May 25 '17 at 13:20
0

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
Pankaj
  • 37
  • 7