Android Lollipop - changed behavior of SQLite Android Lollipop - changed behavior of SQLite sqlite sqlite

Android Lollipop - changed behavior of SQLite


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.


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".


I'm seeing a couple problems with the OP.

  1. FTS uses percent not asterisk as a wild card.

  2. FTS searches on word boundaries so having a leading wild card has never worked with FTS it would've passed it through to sqlite to read the entire table dreadfully slow but the OP leads us to believe it did work in the past. Hence this query would've ran slow in the past if it did run at all.

  3. Union queries are always excessive in the amount of resources they use especially when combined with some group by. Hence this query has no place in the scalable business application.