[SOLVED] SQLite FTS example doesn't work

SQLite FTS example doesn't work

I've 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 in spite of last comment SELECT resulted in empty set. Is it a bug in SQLite or just outdated documentation? (and what is the correct syntax for that?).

What is most important for me is that query

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

doesn't work either and that type of queries I need in my app. Is there any other way to write it so it would work?

Solution

The example from the documentation uses the enhanced query syntax. Check that PRAGMA compile_options; includes ENABLE_FTS3_PARENTHESIS.

That your NEAR query does not work 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 does work only with basic search expressions:

A NEAR query is specified by putting the keyword "NEAR" between two phrase, term or prefix queries.

So you have to rewrite your search expression accordingly:

> 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