Android Lollipop - changed behavior of SQLite

Question

When testing one of my apps for Android 5.0 compatibility I found that one two of my SQL queries doesn't don't work as expected anymore on Lollipop. Both of my problems led to significantly different results on Lollipop compared to older Android versions.

Below, I will describe those problems and their solutions more deeply in case you have similar issues.

My main question is quite simple: Are those non-backwards compatible changes somewhere documented?

Problem number one: MATCH

It seems that the following query doesn't work anymore on Lollipop:

SELECT title FROM ents JOIN ctt ON ctt.docid = ents.cttId WHERE (ctt MATCH '*ads*');

It does not return any results anymore, on pre-Lollipop it did (with the same database and the same data, of course).

As described in this question, for example, MATCH matches only string prefixes. That's actually true, the '*' in front of the search term was just ignored on Android < 5.0.

Lollipop's SQLite, however, doesn't like the first '*' and doesn't return anything for this query. I had to change the query to the following to make it work again:

SELECT title FROM ents JOIN ctt ON ctt.docid = ents.cttId WHERE (ctt MATCH 'ads*');

(I am using FTS3 for full text search.)

Problem number two: COLLATE LOCALIZED

Short story: GROUPing BY an aliased column referenced by the original name in conjunction with an ORDER BY using the Android-specific "COLLATE LOCALIZED" throws an error on Lollipop, but works on previous versions. WTF!? :-)

Long story:

The story began with a quite large automatically generated query, so I modified, simplified and shortened it to the parts which cause the problems. I am aware of the fact that the query doesn't make much sense as shown below, but it demonstrates the problem.

SELECT 
    inner.title AS title, 
    ltrim(inner.title, '*') AS title2
FROM 
    (SELECT types.text AS title FROM types) AS inner
GROUP BY inner.title 

UNION SELECT 
    inner.title AS title, 
    ltrim(inner.title, '*') AS title2
FROM 
    (SELECT types.text AS title FROM types) AS inner
GROUP BY inner.title 

ORDER BY title2 COLLATE LOCALIZED ASC

The query above works on Andriod < 5.0, but results in an error in Lollipop:

Error: no such column: inner.title

OK, I aliased "inner.title" with "title", so I tried changing the "GROUP BY inner.title" to "GROUP BY title" which really is the solution for Lollipop's SQLite:

SELECT 
    inner.title AS title, 
    ltrim(inner.title, '*') AS title2
FROM 
    (SELECT types.text AS title FROM types) AS inner
GROUP BY title 

UNION SELECT 
    inner.title AS title, 
    ltrim(inner.title, '*') AS title2
FROM 
    (SELECT types.text AS title FROM types) AS inner
GROUP BY title 

ORDER BY title2 COLLATE LOCALIZED ASC

(btw, in this answer you can find a great overview of the used SQLite versions in Android)

Now comes the interesting part: If the Android specific "COLLATE LOCALIZED" is removed in the ORDER BY clause everything starts working too, even with "GROUP BY inner.title":

SELECT 
    inner.title AS title, 
    ltrim(inner.title, '*') AS title2
FROM 
    (SELECT indsntyps.text AS title FROM indsntyps) AS inner
GROUP BY inner.title 

UNION SELECT 
    inner.title AS title, 
    ltrim(inner.title, '*') AS title2
FROM 
    (SELECT indsntyps.text AS title FROM indsntyps) AS inner
GROUP BY inner.title 

ORDER BY title2 ASC

My Lollipop experiences are based on tests in the SDK's emulator using the Android 5.0 - API Level 21 ARM system image.

This secon problem seems like an Android-specific SQLite bug to me. Or can someone explain me this (to my eyes) weird behavior? Or, again, is this even somewhere documented? :-)

Thanks in advance!

Answer 1

I'm not a SQLite expert by any means, and I assume you intended for this question to be largely rhetorical, but allow me to offer some thoughts.

MATCH

As you've already indicated, MATCH only considers prefix terms. It's not surprising that unexpected and unpredictable behaviors would arise from prefixing the prefix (if you will) with an asterisk.

COLLATE LOCALIZED with an alias

This appears to be an interesting bug. You can try and use EXPLAIN QUERY PLAN to try and diagnose it, though.

Documentation

Obviously, I haven't told you anything you don't already know. Your "question" was about documentation, however. For starters, the SQLite release notes can be found here. They are usually fairly detailed about changes between releases.

Your first issue is really a programming error. The documentation is already there on how to use FTS prefixes. You're not going to get an explanation as to why your particular syntax stopped working. Arguably, it never should have worked to begin with.

The LOCALIZED issue is likely a bug thus its lack of "documentation" (I encourage you to report it to Google, though). Also remember that SQLite is part of the Android core, and that has not only customizations (like LOCALIZED) but also native Java bindings. Both the underlying SQLite core implementation and the bindings are potentially changing with every release. Which brings me to my main point:

Consider deploying your app with a private SQLite implementation. The instructions to do so can be found here. This will enable you to control the version of SQLite your app uses and give you fine control on how and when to upgrade it. This does come at a cost, however, as you lose the LOCALIZED keyword, for example and the bindings only support API 15 or higher, I believe.

Answer 2

The changes observed are because Lollipop ships with SQLite 3.8 (Android 4.x shipped with 3.7.11). Here is the list of changes http://www.sqlite.org/releaselog/3_8_0.html

For example, the error with no such column for "GROUP BY inner.title" is due to this "Identifiers in GROUP BY clauses always prefer output column names".