SQLite Forum

Unable to use FTS MATCH with OR condition
Login

Unable to use FTS MATCH with OR condition

(1) By Victor K (victor.k) on 2021-08-15 02:30:47 updated by 1.1 [source]

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!


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


```

Unable to use FTS MATCH with OR condition

(1.1) By Victor K (victor.k) on 2021-08-15 03:42:11 edited from 1.0 [link] [source]

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


(2) By Victor K (victor.k) on 2021-08-15 02:33:02 in reply to 1.0 updated by 2.1 [link] [source]

My SQLite version is 3.34.0.

(2.1) By Victor K (victor.k) on 2021-08-15 03:41:28 edited from 2.0 in reply to 1.0 [link] [source]

Deleted

(3) By Victor K (victor.k) on 2021-08-15 06:18:49 in reply to 1.1 [link] [source]

I found the following SO question from 2015, which indicates that FTS3 had a similar bug once, but unfortunately the linked mail list archive is not available anymore.

(4) By Max (Maxulite) on 2021-08-15 09:36:51 in reply to 3 [link] [source]

The post is available at mail-archive. Nabble has probably got rid of most of the servers so the current non-availability of the posts is very likely permanent.

(5) By Victor K (victor.k) on 2021-08-15 16:30:32 in reply to 4 [link] [source]

Thank you Max - the link to mail archive was very helpful. They had a similar problem with FTS3 queries back in 2010, and the suggested solution was to use sub selects, as building queries with virtual tables is finicky. E.g. the following works as expected and solves my immediate need:

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.rowid IN (SELECT rowid FROM address_fts WHERE address_fts MATCH 'google')
    OR
    ta.rowid IN (SELECT rowid FROM address_fts WHERE address_fts MATCH 'google')
LIMIT 20

This solves my problem, as my queries are auto-generated and it's not hard to change the code to generate a slightly different expression.

Looks like I was under a mistaken assumptions that VIRTUAL TABLE behaves exactly like a normal table, with queries generated using the same rules etc. Maybe it's worth mentioning the difference somewhere in the documentation?