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

本教程将介绍SQLite 外部查询返回内部查询中未找到的结果的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

SQLite 外部查询返回内部查询中未找到的结果 教程 第1张

问题描述

我只是想知道是否有人在 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.我对子查询怎么工作的理解是,它应该返回相同的结果内部查询已物化,外部查询应用于这些结果(即使我意识到数据库的长度很长除非必要,否则不要实现结果.

我的假设不正确吗?我在 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 邮件列表将此作为错误报告,并将其作为解决方法.

推荐答案

我可以验证它是否也适用于 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 正在添加大量操作、内联和优化内部查询(不正确).

好了关于SQLite 外部查询返回内部查询中未找到的结果的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。