Android的棒棒糖 - 的SQLite改变行为 [英] Android Lollipop - changed behavior of SQLite

问题描述

当为Android 5.0兼容性测试我的应用程序之一，我发现<删除>一个我的两个SQL查询<删除>不没有如预期再上棒棒堂工作。我的两个问题导致了对棒棒堂显著不同的结果相比老的Andr​​oid版本。

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?

看来，下面的查询不棒棒糖工作了：

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*');

有不返回任何结果了，上$ P $对 - 棒棒糖（当然具有相同的数据库和相同的数据，）它确实

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

作为<一个描述href="http://stackoverflow.com/questions/11752840/ends-with-suffix-and-contains-string-search-using-match-in-sqlite-fts">this问题，例如，MATCH仅匹配字符串prefixes。在搜索词的前面这实际上是真正的'*'只是忽略了在Android＆LT; 5.0。

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.

棒棒堂的SQLite的，但并不像第一个*，并没有为这个查询返回任何东西。我只好查询更改为以下内容，以使其能够工作：

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*');

（我用FTS3全文搜索。）

(I am using FTS3 for full text search.)

简单地说：分组由最初的名字引用结合一个ORDER BY采用了Android特有的分页本地化的别名的列抛出的棒棒糖错误，但适用于previous版本。 WTF！？ ： - ）

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!? :-)

龙的故事：

这个故事开始于一个相当大的自动生成的查询，让我修改，简化和缩短它引起的问题的部分。我知道一个事实，即查询没有多大意义，如下图所示，但它说明了问题。

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

上面的查询适用于安卓＆LT; 5.0，但结果在棒棒堂的错误：

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

Error: no such column: inner.title

OK，我别名inner.title与题，所以我试图改变GROUP BY inner.title到GROUP BY称号，这真的是棒棒堂的SQLite的解决方案：

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

（顺便说一句，在这个答案你可以找到使用的SQLite版本的Andr​​oid的一个很好的概述）

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

现在到了有趣的部分：如果在Android特有的逐份打印本地化的顺序是去除BY子句一切开始工作压力太大，即使有GROUP BY inner.title：

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

我的棒棒糖经验的基础上采用了Android 5.0测试在SDK的模拟器 - API等级21 ARM系统映像

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

这塞康的问题似乎是一个Android特有的SQLite的错误给我。或者有人可以解释我这个（我的眼睛）怪异的行为？或者，再次，这是甚至某处记录？ ： - ）

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? :-)

在此先感谢！

推荐答案

我不是一个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 只考虑preFIX条款。这并不奇怪，意外和未predictable行为将从$ P $出现pfixing的preFIX（如果你愿意）有星号。

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.

这似乎是一个有趣的问题。你可以尝试使用 EXPLAIN查询规划来尝试和诊断，虽然。

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

很显然，我还没有跟你说什么你不知道的。你的问题是有关文档，但是。对于初学者来说， SQLite的发行说明可以在这里找到。他们通常是相当详细了解版本之间的变化。

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.

您的第一个问题是一个真正的编程错误。该文件已经存在，如何使用FTS prefixes。你不会得到一个解释，为什么您的特定语法停止工作。可以说，它根本就不应该合作的开始。

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.

在中局部的问题可能是一个错误从而缺乏文件（我鼓励你向它报告给谷歌，虽然）的。还记得SQLite是Android的核心组成部分，而不仅自定义设置（如中局部），而且本地Java绑定。无论是底层SQLite的核心实现和绑定可能与每一个版本发生变化。这让我想起我的主要观点：

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:

考虑与私人SQLite的实现部署的应用程序。该指令的话可以在这里找到。这将使你能够控制的SQLite您的应用程序使用的版本，并给你何时以及如何升级它很好的控制。这确实付出了代价，但是，因为你失去了中局部关键字，例如和绑定只支持API 15或更高，我相信。

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.

这篇关于Android的棒棒糖 - 的SQLite改变行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！