SQLite FTS4 enhanced query syntax behaviour (use of parenthesis)

96 Views Asked by At

I cannot really understand the behaviour of parenthesis with FTS4, and would appreciate some explanations.

Here are 3 variants of a query which, to my understanding, should all provide similar results, and which are not:

sqlite> .version
SQLite 3.28.0 2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50 
zlib version 1.2.11 
gcc-7.4.0
sqlite> -- Query 1 (1 result) 
sqlite> SELECT id_ph FROM jmcfts4 WHERE jmcfts4 MATCH '( cancan* ) OR ( can can )' ;
97
sqlite> -- Query 2 (2 results) 
sqlite> SELECT id_ph FROM jmcfts4 WHERE jmcfts4 MATCH 'cancan* OR ( can can )' ;
97
265
sqlite> -- Query 3 (12 results) 
sqlite> SELECT id_ph FROM jmcfts4 WHERE jmcfts4 MATCH 'cancan* OR "can can"' ;
9
96
97
100
101
143
144
145
152
161
265
266

Interestingly, with SQLite version 3.32.2 (embedded in DB Browser 3.12), the 3 variants do actually output the same set of 12 results (the expected results, btw).

Using FTS5 (whatever the version of SQLite), I do also get the same set of 12 results for each 3 variants of the query.

Unfortunately, at the moment I need to stick to rather old versions of SQLite.

Any hint on why parenthesis do not seem to do their part in the above queries?

Thanks

0

There are 0 best solutions below