SQLite Forum

fts5: NOT only queries

fts5: NOT only queries

(1) By rohfle on 2021-10-09 00:59:53 [source]


I have created an fts5 table for fun with denormalized data from a model with many relations in the hope to simplify search. This fts5 table has around 2000 rows and queries on this table work mostly the same to what I was doing before.

One of the use cases that doesn't work is queries that only subtract rows from the results. ie only phrases that only have NOT foo NOT bar. Alternatively, there is no way to select all rows explicitly first and then only subtract.

sqlite> select * from fts5table where fts5table not match "foo"; Error: unable to use function MATCH in the requested context sqlite> select * from fts5table where fts5table match "* NOT foo"; Error: unknown special query: sqlite> select * from fts5table where fts5table match "NOT foo"; Error: fts5: syntax error near "NOT"

For simple queries, a user application workaround could be transformation of the query using DeMorgan's Theorem from NOT a AND NOT b AND NOT c to NOT (a OR b OR c)

And then using a subquery like SELECT * from data d WHERE d.id NOT IN (SELECT rowid FROM fts5table WHERE fts5table MATCH "a OR b OR c")

This method requires an extra layer of complexity (user-side fts5 query parsing / rendering, recognition of only subtractive logic, ignoring groups)

If this were to be supported in fts5, it would be pointless to use rank, context and highlight for these queries, but it would be very useful for discovery by elimination on smallish datasets.

If anyone has ideas how to implement this in code, or any thoughts / side effects why it would be difficult or not a good idea, I would be interested in hearing them.

References to previous discussions