SQLite Forum

Unable to use FTS MATCH with OR condition
Login
Hello,

I'm a bit puzzled that a query below containing two MATCH statements joined with a logical operator works when the operator is AND but doesn't work when the operator is OR.

```
SELECT e.id
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
WHERE
    (fa.address_fts MATCH 'google')
    OR   -- the query works with AND and fails with OR
    (ta.address_fts MATCH 'gmail')
LIMIT 20
```

With AND, the statement is executed and a result is returned. With OR, I get the following error message: 

```[1] [SQLITE_ERROR] SQL error or missing database (unable to use function MATCH in the requested context)```

My tables are defined as follows - I use tables `email_address_from` and `..._to` as many-to-many relationship between tables `email` and `address`. I populate `address_fts` with triggers as prescribed by the documentation but omitted it for brevity.

I will appreciate any suggestions on how to properly fix or further debug this!

PS. I checked with both 3.34.0 and 3.36.0 version of SQLite on macOS.


```
CREATE TABLE email (id INTEGER PRIMARY KEY, subject TEXT, body TEXT);

CREATE TABLE address (id INTEGER PRIMARY KEY, name TEXT, address TEXT);

CREATE TABLE email_address_from(
 email_id INTEGER, 
 address_id INTEGER, 
 FOREIGN KEY(email_id) REFERENCES email(id), 
 FOREIGN KEY(address_id) REFERENCES address(id));

CREATE TABLE email_address_to(
 email_id INTEGER, 
 address_id INTEGER, 
 FOREIGN KEY(email_id) REFERENCES email(id), 
 FOREIGN KEY(address_id) REFERENCES address(id));

CREATE VIRTUAL TABLE address_fts USING fts5(name, address, content='address', content_rowid='id');


```