Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

On Sat, Sep 20, 2014 at 12:57 PM, Clemens Ladisch wrote: > Yuanzhong Xu wrote: > > There is a related issue: > > > > If you use this valid efficient query as a subquery of SELECT id FROM > (...), i.e., > > > > SELECT id FROM (SELECT id,data FROM (SELECT * FROM t1 UNION ALL

Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

Yuanzhong Xu wrote: > There is a related issue: > > If you use this valid efficient query 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

Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

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

Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

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.

Re: [sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

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

[sqlite] Inefficient query plan with ORDER BY and UNION ALL subquery

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 *