1

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

gfc
  • 33
  • 4
  • The version of SQLite you're testing on probably wasn't compiled with support for the enhanced fts3/4 syntax. Look for it in `pragma compile_options` output in the ones your queries are and aren't working on. – Shawn Jun 21 '20 at 15:42
  • Thanks, Shawn. Not sure which option label to look for. The problematic version says: `COMPILER=gcc-7.4.0 ENABLE_API_ARMOR ENABLE_COLUMN_METADATA ENABLE_DBSTAT_VTAB ENABLE_FTS4 ENABLE_FTS5 ENABLE_HIDDEN_COLUMNS ENABLE_JSON1 ENABLE_RBU ENABLE_RTREE ENABLE_UNLOCK_NOTIFY SECURE_DELETE SOUNDEX THREADSAFE=1` whereas the latest (ok) version says: `COMPILER=msvc-1916 ENABLE_FTS3 ENABLE_FTS3_PARENTHESIS ENABLE_FTS5 ENABLE_GEOPOLY ENABLE_JSON1 ENABLE_RTREE ENABLE_STAT4 MAX_ATTACHED=125 SOUNDEX THREADSAFE=1 ` would `ENABLE_FTS3_PARENTHESIS` be the one to expect? – gfc Jun 21 '20 at 17:32
  • Yup, that's the one that turns on extended syntax. – Shawn Jun 21 '20 at 21:47
  • Thanks. Actually I won't be in a position to recompile the engine in every context of use. So I guess I have to go with an alternative syntax. The safest here seems to be a UNION on the whole SELECT/FROM/WHERE. I haven't really measured the impact on performance but I'm only building prototypes, so it's not my major concern. Thanks again for your help. – gfc Jun 21 '20 at 22:26
  • This is why I include a copy of SQLite in my projects instead of relying on whatever random system one is installed. Easier to do with C and C++ than other languages, of course. – Shawn Jun 22 '20 at 03:48

0 Answers0