Cannot use match on SQLite FTS5 virtual table: unable to use function MATCH in the requested context

Question

A simple test fails when trying to work with FTS5 in SQLite 3.13.0. What am I doing wrong?

SQLite version 3.13.0 2016-05-18 10:57:30
CREATE VIRTUAL TABLE testfts USING FTS5(test);
INSERT INTO testfts VALUES("some test string");
SELECT * FROM testfts WHERE test MATCH 'test';
Error: unable to use function MATCH in the requested context

redneb · Accepted Answer · 2016-09-17 20:15:07Z

17

Try this instead:

SELECT * FROM testfts WHERE testfts MATCH 'test';

or

SELECT * FROM testfts WHERE testfts MATCH 'test:test';

The first one will search all columns of the table testfts for the string test. The second one will restrict the search only to the column test (that's what the test: prefix in the query string does). The two are equivalent here since there is only one column.

answered Sep 17, 2016 at 20:15

redneb

24.3k7 gold badges46 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2005218 Over a year ago

Excellent! Thank you so much. It appears I didn't understand the true nature of FTS virtual tables.

BangOperator Over a year ago

@redneb What if I had total 3 rows and would like to match ORing just two rows?

redneb Over a year ago

You have 3 options: (1) use a boolean expression inside the fts query, e.g. testfts MATCH 'col1:test OR col2:test', (2) mark the third column as UNINDEXED (look at the manual for more details), (3) move the third column to a different table.

Reihan_amn Over a year ago

I have a table with two col of id, content. If I query like : [q = 'SELECT id, content FROM table1 WHERE content MATCH 'university' ORDER BY bm25(content) DESC;'] it doesn't work!! But if I query for all columns, it works! any idea?

redneb Over a year ago

@Reihan_amn Please read my answer more carefully. The WHERE clause must always reference the table, not the column, i.e. in your case WHERE table1 MATCH '...'. If you need to match a specific column, you have to reference it from inside the query, as per my second example in the answer above.

|

Collectives™ on Stack Overflow

Cannot use match on SQLite FTS5 virtual table: unable to use function MATCH in the requested context

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related