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

=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

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

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, -- Yuanzho