On 04/29/2016 09:41 PM, Josef Ku?era wrote:
> Hello,
> I have discovered a possible query planner improvement. I am using the SQLite
> with virtual table modules.
>
> Situation 1: Lets have a query like 'SELECT V.* FROM (select A.RowID A_ROWID,
> B.RowID B_ROWID, A.*, B.* from A left outer join B on B.ref=A.key) V WHERE
> V.B_ROWID=?', where A and B are virtual tables. If I am not mistaken the
> WHERE term effectively turns the outer join to an inner join and the query
> could be evaulated as two fast key searches. Unfortunately, currently the
> join is left as is and the execution peforms a sequentail full-scan of the A
> table with many key searches to the B table.
>
> Situation 2: How does SQLite evaluate tables to the left of a left join? As
> it seems it is done by order in the SQL. So query like this: select * from A
> join B on B.ref=A.key left outer join C on C.ref=A.key where C.Value=? causes
> a full scan of A and B before even evaulating C. By looking at the query the
> B table does not seem to be a pre-requisite of table C, although is it marked
> as one in where trace.
At the moment, SQLite processes everything to the left of the LEFT (or
CROSS) JOIN before everything to the right of it.
Dan.
