Android Sqlite FTS NOT Operation

Question

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

I've 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

So the result would 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 that works great but when I use the same query in android I only 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 seems to be interpreting the:

MATCH 'dog* NOT dog'

as

MATCH 'dog* not dog'

Whats going on?

Villainous

So your problem is that when you search for handcar, SQLite finds dog? Anyway, instead of UNION and _order, you could just use UNION ALL. – Stravinsky

Answer 1

Android FTS uses Standard Query Syntax, Not Enhanced.

As such, it does not recognize "NOT" or "AND" as operators. It's actually trying to match them to the text of your db columns. Which is why on the Android, your only match came from an entry with "not" in the actual text.

You have to use "-" for NOT and blank space for AND. Thus, your syntax should look like:

WHERE glossfts.value MATCH 'dog* -dog'

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

Deconsecrate

Answer 2

Apparently, the SQLite on that Android device has be compiled with a different FTS query syntax.

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

Stravinsky

I had a look and my android device has no compile options, so I guess i should just avoid the enhanced query syntax and use something like WHERE glossfts.value MATCH "dog*" AND glossfts.rowid NOT IN (SELECT glossfts.rowid FROM glossfts WHERE glossfts.value MATCH "dog") instead? – Villainous