SQLite外部查询返回内部查询中找不到的结果 [英] SQLite outer query is returning results not found in inner query
问题描述
我只是想知道是否有人在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.
编辑：通过添加，我能够禁用不正确的子查询折叠偏移量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).
这篇关于SQLite外部查询返回内部查询中找不到的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
