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
