Outer query returning results not found in subquery

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Outer query returning results not found in subquery

Dan Kubb (dkubb)
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Outer query returning results not found in subquery

Igor Tandetnik
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

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Outer query returning results not found in subquery

Shane Harrelson-2
On Thu, Feb 10, 2011 at 2:29 PM, Igor Tandetnik <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users