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. 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
