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. Is this correct? Thanks. Joe
