SQLite 外部查询返回内部查询中找不到的结果

Question

我只是想知道是否有人遇到过 SQLite (3.7.4) 中的情况，其中查询将返回一组结果，而当它成为子查询时，结果完全不同？我在更复杂的查询中发现了问题，但这里有一个更简单的示例，演示了相同的行为：

数据库设置：

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< /code>，结果的第二行，正如您所期望的那样，因为我们先按字母排序，然后按数字排序。然而，这是我没想到的：

作为子查询的初始查询：

SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1) AS "test";

这将返回1，这根本不是我所期望的。我期望看到的是 2。我对子查询如何工作的理解是，它应该返回相同的结果就像内部查询已具体化，并且外部查询针对这些结果应用（即使我意识到数据库会达到极端长度）除非必要，否则不要实现结果）。

我的假设不正确吗？我在 PostgreSQL 和 MySQL 中测试了相同的查询，它按我的预期工作（即返回 2）。在我看来，我遇到了 SQLite 折叠子查询的错误，但我不确定。

重申一下，上面的例子是我实际做的简化的。我不只是在返回单行的子查询上使用 DISTINCT，而是返回许多行，其中一些行具有相同的列值，因此我需要 DISTINCT。上面的例子是我能想到的演示正在发生的事情的最简单的方法。

编辑：我能够通过将 OFFSET 0 添加到内部查询来禁用不正确的子查询折叠，例如，

SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1 OFFSET 0) AS "test";

我将通过 SQLite 邮件列表将此报告为错误，并将其作为解决方法。

Answer 1

我可以验证 Firefox 的 SQLite 附加组件是否也会发生这种情况。

如果有什么安慰的话，这种形式是有效的：

SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test"
ORDER BY "letter", "number") AS "test" ORDER BY "letter" LIMIT 1;

我相信 SQLite 规范会忽略内部查询中的 LIMIT 子句并将其迁移到外部。没有限制：

SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test"
ORDER BY "letter", "number") AS "test";

它返回

1
2
(2 rows)

有趣的是，这也返回正确的结果

SELECT number FROM (SELECT letter, number FROM test
ORDER BY letter, number LIMIT 1) AS test;

这两个计划可以使用 EXPLAIN 进行比较。
DESCRIBE 正在添加大量操作、内联和优化内部查询（错误地）。