On Thu, Feb 10, 2011 at 2:29 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> On 2/10/2011 2:17 PM, Dan Kubb wrote:
>> 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"
>
> Yes, looks like a bug. If you drop DISTINCT, it returns a single row
> with the value 2.
>
> My guess is, DISTINCT is internally implemented as ORDER BY, and that
> overrules ORDER BY found in the subquery.
> --
> Igor Tandetnik
>
Thanks for the report and recreate. A ticket was opened here:
http://www.sqlite.org/src/tktview/752e1646fcc7b649184e49783577a7feb5f7fc9c
I was able to recreate the problem. Additionally, if query
optimizations are disabled, then the correct result is returned, so
this is most likely and issue with the query flattener.
-Shane
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
