External SQLite query returns results not found in the internal query

Question

I just wondered if anyone came across in the case of SQLite (3.7.4), where the query will return one set of results, and when it becomes a subquery, the results will be completely different? I found the problem in a more complex query, but here is a simpler example demonstrating the same behavior:

Database setup:

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); 

Original request:

 SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1; 

This returns a|2 , the second row from the results, as you would expect, given that we sort by letter, and then the number. However, here is what I did not expect:

Original request as a subquery:

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

This returns 1 , which is not at all what I expected. I expected to see 2 . My understanding of how the subquery works is that it should return the same results as the internal query, and the external query was applied to these results (although I understand that the databases go to extreme lengths so as not to deliver the results until necessary).

Is my assumption wrong? I tested the same query in PostgreSQL and MySQL, and it worked as I expected (i.e. it returned 2 ). It seems to me that I ran into an error in how SQLite collapses subqueries, but I'm not sure.

To repeat, the above example is simplified from what I'm actually doing. I'm not just using DISTINCT in a subquery that returns a single row, but rather returns many rows, some of which have the same value for the column, so my need for DISTINCT. The above example is the easiest way I could imagine to demonstrate what is happening.

EDIT: I managed to disable the falsification of the wrong subquery by adding OFFSET 0 to the internal query, for example.

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

I will talk about this as an error on the SQLite mailing list, and this will work.

Answer 1

I can verify that this is happening with the SQLite add-on for Firefox.

If this is a consolation, this form works:

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

I believe that the SQLite specification ignores the LIMIT clause in internal queries and wraps it outside. Without limitation:

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

He returns

 1 2 (2 rows) 

It is 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; 

Two plans can be compared using EXPLAIN.
DESCRIBE adds many operations by embedding and optimizing the internal query (incorrectly).