Android Sqlite FTS NOT Operation

Question

I'm trying to order a quick text search so that matches are first and partial matches are last.

I created a query that works in SQLiteStudio:

SELECT value, 1 AS _order FROM glossfts WHERE glossfts.value MATCH 'dog' UNION SELECT value, 2 AS _order FROM glossfts WHERE glossfts.value MATCH 'dog* NOT dog' ORDER BY _order 

Thus, the result will be

 Beware of dog 1 Disliked by everybody, not even a dog will eat 1 Bad dog 1 Creed, dogma 2 Dogs 2 Dogwood 2 

And this works fine, but when I use the same request in android, I get

 Beware of dog 1 Disliked by everybody, not even a dog will eat 1 Bad dog 1 Disliked by everybody, not even a dog will eat 2 

back as it interprets:

 MATCH 'dog* NOT dog' 

but

 MATCH 'dog* NOT dog' 

What's happening?

Answer 1

Android FTS uses standard query syntax, not advanced.

Thus, he does not recognize “NOT” or “AND” as operators. It actually tries to match them with db column text. That is why on Android, your only match came from a record with a no in the actual text.

You must use a "-" for NOT and a space for AND. So your syntax should look like this:

WHERE glossfts.value MATCH 'dog * -dog'

http://www.sqlite.org/fts3.html

Answer 2

Apparently, SQLite on this Android device was compiled with a different FTS query syntax .

Check the output of PRAGMA compile_options; for ENABLE_FTS3_PARENTHESIS and configure your queries accordingly.