Geek Answers Handbook

SQLite FTS example not working

I downloaded the latest SQLite 3.7.15.2 shell (Win32) and tried to execute one of the FTS examples exactly as it is written at http://sqlite.org/fts3.html#section_3

-- Virtual table declaration CREATE VIRTUAL TABLE docs USING fts3(); -- Virtual table data INSERT INTO docs(docid, content) VALUES(1, 'a database is a software system'); INSERT INTO docs(docid, content) VALUES(2, 'sqlite is a software system'); INSERT INTO docs(docid, content) VALUES(3, 'sqlite is a database'); -- Return the set of documents that contain the term "sqlite", and the -- term "database". This query will return the document with docid 3 only. SELECT * FROM docs WHERE docs MATCH 'sqlite AND database';

but despite the last comment, SELECT ended with an empty set. Is this a bug in SQLite or just plain outdated documentation? (and what is the correct syntax for?).

What is most important to me is the request

 SELECT * FROM docs WHERE docs MATCH '(database OR sqlite) NEAR/5 system';

this type of queries that I need in my application also does not work. Is there any other way to write it so that it works?

+4
source share
2 answers

The sample documentation uses the extended query syntax . Make sure PRAGMA compile_options; includes ENABLE_FTS3_PARENTHESIS .

That your NEAR request is not working is not a problem with compilation options:

 > SELECT * FROM docs WHERE docs MATCH '(database OR sqlite) NEAR/5 system'; Error: malformed MATCH expression: [(database OR sqlite) NEAR/5 system]

The problem is that, according to the documentation, NEAR only works with basic search expressions:

A NEAR query is specified by placing the keyword "NEAR" between two phrases, terms, or prefixes.

Therefore, you should rewrite your search expression as follows:

 > SELECT * FROM docs WHERE docs MATCH '(database NEAR/5 system) OR (sqlite NEAR/5 system)'; a database is a software system sqlite is a software system
+3
source

I don’t know if this is a document or an error with SQLite, but here are a few alternatives:

For AND queries

Does not work:

 select * from docs where docs match 'sqlite AND database';

Works (using implied AND ):

 select * from docs where docs match 'sqlite database';

OR seems to work:

 select * from docs where docs match 'sqlite OR database';

For OR + NEAR queries:

Does not work:

 SELECT * FROM docs WHERE docs MATCH '(database OR sqlite) NEAR/5 system';

Works:

 SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 system' UNION SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/5 system'

EDIT: for the form specified in the comments (word11 OR word12 OR word13) NEAR/2 (word21 OR word22 OR word23) NEAR/2 (word31 OR word32 OR word33 . This is the best I can do is put all the combinations together with UNION :

 SELECT * FROM docs WHERE docs MATCH 'word11 NEAR/2 word21 NEAR/2 word31' UNION SELECT * FROM docs WHERE docs MATCH 'word11 NEAR/2 word22 NEAR/2 word32' UNION SELECT * FROM docs WHERE docs MATCH 'word11 NEAR/2 word23 NEAR/2 word33' UNION SELECT * FROM docs WHERE docs MATCH 'word12 NEAR/2 word21 NEAR/2 word31' ...

The above, of course, creates large amounts of SQL. If your words are alike, then only the final meanings are different, you can use wildcards:

 SELECT * FROM docs WHERE docs MATCH 'word1* NEAR/2 word2* NEAR/2 word3*';
+3
source

More articles:


All Articles