我只是想知道是否有人遇到过 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
,结果的第二行,正如您所期望的那样,因为我们先按字母排序,然后按数字排序。然而,这是我没想到的:
作为子查询的初始查询:
SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1) AS "test";
这返回1
,这根本不是我所期望的。我期望看到的是2
。我对子查询如何工作的理解是它应该返回相同的结果as if内部查询被具体化,外部查询被应用于这些结果(尽管我意识到数据库会竭尽全力在必要时不具体化结果)。
我的假设不正确吗?我在 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 邮件列表将此作为错误报告,并将其作为解决方法。