0

I want to perform a query in which the WHERE clausule has the following condition:

  • one MATCH condition over a column in a FTS3 table OR
  • another not MATCH condition over a column in a non FTS table.

Example:

Say that I have two tables

  • books_fts, which is a table with a content column for full text search.
  • books_tags, which is non FTS table with tags.

I want to search all the books that either contain 'Dikjstra' in their content or are tagged with the 'algorithm' word. So I run this query:

SELECT * from books_fts               
LEFT OUTER JOIN books_tags ON books_fts.fk_id = books_tags.id
WHERE (books_fts MATCH 'content:%Dijkstra*')
OR (books_tags.tag = 'algorithm')

I think the query is right, and if I run it with either one of the OR clausules, it works. However, when running it with the two clausules I get the following error:

unable to use function MATCH in the requested context

Seems to me that I cannot combine a MATCH with a non MATCH in the WHERE clause, even if each of them apply to different tables (one FTS and another non FTS). Is this true? I cannot find information on it.

NOTE: if the causules are separated with AND instead of OR the query is valid.

Thanks.

GaRRaPeTa
  • 5,459
  • 4
  • 37
  • 61

1 Answers1

1

It seems it's a known issue in SQL:

http://sqlite.1065341.n5.nabble.com/FTS3-bug-with-MATCH-plus-OR-td50714.html

GaRRaPeTa
  • 5,459
  • 4
  • 37
  • 61