There is a related issue:
If you use this valid efficient query ,
SELECT id,data FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)
WHERE id=10 ORDER BY data;
as a subquery of SELECT id FROM (...), i.e.,
SELECT id FROM (SELECT id,data FROM (SELECT * FROM t1 UNION ALL SELECT
* FROM t2) WHERE id=10 ORDER BY data);
SQLite reports error:
Error: 1st ORDER BY term out of range - should be between 1 and 1
Thanks,
Yuanzhong
On Sat, Sep 20, 2014 at 5:36 AM, Dan Kennedy <danielk1...@gmail.com> wrote:
> On 09/20/2014 10:59 AM, Yuanzhong Xu wrote:
>>
>> I think this is related to a check for restriction (18) in subquery
>> flattening.
>>
>> (18) If the sub-query is a compound select, then all terms of the
>> ORDER by clause of the parent must be simple references to
>> columns of the sub-query.
>
>
> Quite correct. Internally, SQLite 'flattens' the first query to:
>
>SELECT id, data FROM t1 WHERE id=10
> UNION ALL
>SELECT id, data FROM t2 WHERE id=10
>ORDER BY data
>
> Which can be executed efficiently. But it does not currently do the same
> trick for the second, as:
>
>SELECT id FROM t1 WHERE id=10
> UNION ALL
>SELECT id FROM t2 WHERE id=10
>ORDER BY data
>
> is not a valid statement in SQLite.
>
> Dan.
>
>
>
>>
>> Thanks,
>> Yuanzhong
>>
>> On Fri, Sep 19, 2014 at 8:16 PM, Yuanzhong Xu <y...@utexas.edu> wrote:
>>>
>>> Hi,
>>>
>>> In SQLite 3.8.6, suppose I have two tables in the same format:
>>>
>>> CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
>>> CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
>>>
>>>
>>> The query plan can be quite efficient for:
>>>
>>> EXPLAIN QUERY PLAN SELECT id,data FROM (SELECT * FROM t1 UNION ALL
>>> SELECT * FROM t2) WHERE id=10 ORDER BY data;
>>>
>>> 1|0|0|SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
>>> 2|0|0|SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
>>> 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
>>>
>>> However, if you remove "data" column from the outer SELECT, it becomes
>>> very inefficient:
>>>
>>> EXPLAIN QUERY PLAN SELECT id FROM (SELECT * FROM t1 UNION ALL SELECT *
>>> FROM t2) WHERE id=10 ORDER BY data;
>>>
>>> 2|0|0|SCAN TABLE t1
>>> 3|0|0|SCAN TABLE t2
>>> 1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
>>> 0|0|0|SCAN SUBQUERY 1
>>> 0|0|0|USE TEMP B-TREE FOR ORDER BY
>>>
>>>
>>> Thanks,
>>> --
>>> Yuanzhong Xu
>>> Ph.D. Student
>>> Department of Computer Science
>>> the University of Texas at Austin
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
