Hi, I have a query that when executed stand-alone returns different results than when it's a subquery. At first I wasn't sure if it was just me, so I submitted a question to Stackoverflow with some detail and someone else replied that they could reproduce what I was seeing:
http://stackoverflow.com/questions/4870293/sqlite-outer-query-is-returning-results-not-found-in-inner-query I'll post the question I asked below since I think it explains what I'm seeing relatively clearly, and provides instructions on how to reproduce: -- 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 behaviour: 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); Initial query: 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're sorting on the letter then the number. However, here's what I did not expect: Initial query 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. 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). 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. 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. -- Thanks, Dan Kubb _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
