SQLite enhanced query syntax on Android

Question

I have the following SQLite query which works great on my local machine:

SELECT * FROM ftdata WHERE ftdata MATCH 'phrase1:this AND phrase2:that'

This doesn't seem to return the same resultset on my Android device as it does on my desktop. It returns far less results on Android. This query appears to return the correct results on both:

SELECT * FROM ftdata WHERE ftdata MATCH 'phrase1:this phrase2:that'

However I would ideally like to combine AND and OR queries instead of being forced to use AND.

Are there certain features of the enhanced query syntax that Android doesn't support? Am I using the incorrect syntax in the first instance?

Bradybradycardia

Answer 1

Different Android firmwares use different SQLite versions, but the FTS syntax has not changed for a long time.

Your problem is that most (all?) Android vendors do not enable the enhanced query syntax.

You should restrict yourself to queries that work with both syntaxes. Alternatively, execute PRAGMA compile_options to check whether ENABLE_FTS3_PARENTHESIS is set.

Menstrual

Ouch. Is there any way I can do an OR statement with MATCH? Even that would be enough. – Bradybradycardia

SELECT ... WHERE MATCH a UNION SELECT ... WHERE MATCH b, which essentially runs two searches, and then combines them with the help of a temporary table. – Menstrual

@Menstrual Do you know if the ENABLE_FTS3_PARENTHESIS option is dependant on sdk version or device? Because the new sdk version gets new sqlite version, so it should be dependant on the sdk. I'm not sure if it makes sence, but I would like a clue on when it should be possible to use only enhanced query syntax. – Quarterback

@MortenHolmgaard I explained this in my answer. Which sentence did you not understand? – Menstrual

"Your problem is that most (all?) Android vendors do not enable the enhanced query syntax." Are you sure it is then vendor and not Googles when releasing new Android versions? – Quarterback

Vendors have the ability to change compilation options. (However, a change would not be beckwards compatible.) – Menstrual