SQLite 外部查询返回内部查询中未找到的结果
我只是想知道是否有人在 SQLite (3.7.4) 中遇到过查询将返回一组结果的情况，而当它成为子查询时结果完全不同?我在一个更复杂的查询中发现了问题，但这里有一个更简单的示例来演示相同的行为:
I just wondered if anyone has run into a case in SQLite (3.7.4) where a query would return one set of results, and when it becomes a subquery the results are completely different? I found the problem in a more complex query, but here's a simpler example that demonstrates the same behavior:
数据库设置:
CREATE TABLE "test" ("letter" VARCHAR(1) PRIMARY KEY, "number" INTEGER NOT NULL);
INSERT INTO "test" ("letter", "number") VALUES('b', 1);
INSERT INTO "test" ("letter", "number") VALUES('a', 2);
INSERT INTO "test" ("letter", "number") VALUES('c', 2);
初始查询:
SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1;
这将返回 a|2，即结果中的第二行，正如您所期望的那样，我们先按字母排序，然后按数字排序.然而，这是我没想到的:
This returns a|2, the second row from the results as you would expect given that we're sorting on the letter then the number. However, here's what I did not expect:
作为子查询的初始查询:
SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1) AS "test";
这会返回 1，这完全不是我所期望的.我期望看到的是2.我对子查询如何工作的理解是，它应该返回相同的结果就好像内部查询已物化，外部查询应用于这些结果(即使我意识到数据库的长度很长除非必要，否则不要实现结果.
This returns 1, which is not at all what I expected. What I expected to see is 2. My understanding of how a subquery works is that it should return the same results as if the inner query was materialized, and the outer query was applied against those results (even though I realize that databases go to extreme lengths not to materialize results until necessary).
我的假设不正确吗?我在 PostgreSQL 和 MySQL 中测试了相同的查询，它按我的预期工作(即它返回 2).在我看来，我遇到了 SQLite 如何折叠子查询的错误，但我不确定.
Is my assumption incorrect? I tested the same query in PostgreSQL and MySQL and it worked as I expected (i.e. it returned 2). What it looks like to me is that I've hit a bug in how SQLite collapses subqueries, but I'm not sure.
重申一下，上面的例子是我实际做的简化.我不只是在返回单行的子查询上使用 DISTINCT，而是返回许多行，其中一些行具有相同的列值，因此我需要 DISTINCT.上面的例子是我能想到的最简单的方式来演示正在发生的事情.
Just to reiterate, the above example is simplified from what I'm actually doing. I'm not just using DISTINCT on a subquery that returns a single row, but rather it returns many rows, some of which have the same value for a column hence my need for DISTINCT. The above example is the simplest way I could think of to demonstrate what's happening.
我能够通过将 OFFSET 0 添加到内部查询来禁用不正确的子查询折叠，例如
I was able to disable the incorrect subquery folding by adding OFFSET 0 to the inner query, e.g.
SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1 OFFSET 0) AS "test";
我将通过 SQLite 邮件列表将此作为错误报告，并将其作为解决方法.
I'll be reporting this as a bug through the SQLite mailing list, and this as a work-around.
推荐答案
我可以验证它是否也适用于 Firefox 的 SQLite 插件.
I can verify that it happens with SQLite add-on for Firefox as well.
如果有什么安慰的话，这个表格有效:
If it is any consolation, this form works:
SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test"
ORDER BY "letter", "number") AS "test" ORDER BY "letter" LIMIT 1;
我相信 SQLite 规范会忽略内部查询中的 LIMIT 子句并将其迁移到外部.没有限制:
I believe the SQLite spec ignores the LIMIT clause in inner queries and migrates it outside. Without the limit:
SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test"
ORDER BY "letter", "number") AS "test";
返回
1
2
(2 rows)
有趣的是，这也返回了正确的结果
Interesting to note that this also returns the correct results
SELECT number FROM (SELECT letter, number FROM test
ORDER BY letter, number LIMIT 1) AS test;
可以使用 EXPLAIN 比较这两个计划.
DESCRIBE 正在添加大量操作、内联和优化内部查询(不正确).
The two plans can be compared using EXPLAIN.
DESCRIBE is adding a lot of operations, in-lining and optimizing the inner query (incorrectly).
相关文章