How to find records BETWEEN with FTS5 and MATCH?

Question

How can I search for a (price) range in a SQLite3 database with a FTS5 table?

This is a strongly simplified example table:

CREATE VIRTUAL TABLE fruits USING fts5 (id, name, price);
INSERT INTO fruits (id,name,price) VALUES (1, 'Apple with A', 5);
INSERT INTO fruits (id,name,price) VALUES (2, 'Pineapple with B', 10);
INSERT INTO fruits (id,name,price) VALUES (3, 'Cucumber with C', 20);
INSERT INTO fruits (id,name,price) VALUES (4, 'Melon with D', 25);
INSERT INTO fruits (id,name,price) VALUES (5, 'Kiwi with E', 30);
INSERT INTO fruits (id,name,price) VALUES (6, 'Cucumber with F', 35);
INSERT INTO fruits (id,name,price) VALUES (7, 'Cucumber with G', 40);

The following command returns the expected two records 3 and 7 for Cucumber:

SELECT * FROM fruits WHERE fruits MATCH 'name:Cucumber AND (price:20 OR price:40)';

How can I search for the Cucumbers in the price range 20 to 40 (to include record 6 in the above example)? If I try it with

SELECT * FROM fruits WHERE fruits MATCH 'name:Cucumber AND (price: BETWEEN 20 AND 40)';

or

SELECT * FROM fruits WHERE fruits MATCH 'name:Cucumber AND (price: BETWEEN 19 AND 41)';

I get no result (or error message) at all. Is it not possible to use MATCH and BETWEEN in one query?

---

And in addition: Why does the command

SELECT * FROM fruits WHERE fruits MATCH 'name:C';

return only one record (id: 3) and not 3, 6 and 7, assuming the C in 'Cucumber' will be found too, not only the C in 'with C'?

Answer 1

FTS tables store everything as text; it does not make sense to have the id and price columns in an FTS table.

The only efficient queries on an FTS table are searches for words (and lookups by the internal docid).

You should treat the FTS table not as a table but as an index. Keep the other data in a 'real' table, and do any other queries on that table:

SELECT *
FROM fruits
WHERE id IN (SELECT docid
             FROM fruits_fts
             WHERE fruits_fts MATCH 'Cucumber')
  AND price BETWEEN 20 AND 40;

To search for words beginning with C, you must use a prefix search.