SQLite FTS example doesn't work

Question

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?

do you, by any chance, mean `'sqlite' AND 'database'`? EDIT: bear with me. I've never used sqlite in my life :) — Andreas Grapentin, Feb 02 '13 at 12:33
This is an exact copy-paste from SQLite documentation. I just want it to work as described there — Stan Lagun, Feb 02 '13 at 12:38

chue x · Answer 1 · 2013-02-02T15:06:49.990

3

I don't know if it is the docs or if it is a bug with SQLite, but here are some alternatives:

For AND queries

Doesn't 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:

Doesn't 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 mentioned 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 could do is to put all combinations together with a 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 similar in that only the endings differ, you could use wildcards:

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

edited Feb 02 '13 at 15:06

answered Feb 02 '13 at 14:36

chue x

18,573
7
56
70

Unfortunately UNION doesn't work for me because my queries would be of form (word11 OR word12 OR word13) NEAR/2 (word21 OR word22 OR word23) NEAR/2 (word31 OR word32 OR word33)... – Stan Lagun Feb 02 '13 at 14:39
@StanLagun - Those may be possible, but it is likely they are a little too complicated. I'll take a look and see if I can come up with anything... – chue x Feb 02 '13 at 14:42
@StanLagun - My response above after the `EDIT:`. Those solutions may or may not be what you are looking for. – chue x Feb 02 '13 at 15:08
This would probably do the job but would require UNIONing something like 1000 SELECTs which probably would have performance impact. I've considered SQLite to be very stable and perfectly documented library. So it seems very strange to me that simple example from official manual doesn't work. My hope that the answer why it is so would give me a clue how to solve my main problem in more elegant fashion – Stan Lagun Feb 02 '13 at 15:55

score 3 · Accepted Answer · answered Feb 02 '13 at 21:05

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

score 0 · Answer 3 · answered Oct 20 '20 at 22:53

0

According to the documentation (https://www.sqlite.org/fts3.html), parentheses are not supported by default.

Look at part 2. Compiling and Enabling FTS3 and FTS4.

answered Oct 20 '20 at 22:53

Thomas

401
1
6
11

SQLite FTS example doesn't work

3 Answers3

Linked