Wrong result if DISTINCT used on subquery which uses ORDER BY.
<blockquote><pre>
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);
</pre></blockquote>
Then the following query
<blockquote><pre>
SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1;
</pre></blockquote>
returns "a|2", the second row from the results as you would expect.
However, if this query is used as the subquery of a SELECT DISTINCT
<blockquote><pre>
SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1) AS "test";
</pre></blockquote>
then it returns "1" instead of the expected "2".
If query optimizations are disabled, the SELECT DISTINCT returns "2".
This is most likely an issue with the query flattener.
|