On Wed, Oct 24, 2012 at 4:59 AM, Scholz Maik (CM-AI/PJ-CF42) < maik.sch...@de.bosch.com> wrote:
> Hi, > I have some strange behavior with the query optimizer. > > SQLite version 3.7.7.1 2011-06-28 17:39:05 > > sqlite> create table t1 (a,b); > sqlite> insert into t1 (a,b) values (1,2); > sqlite> insert into t1 (a,b) values (3,4); > > sqlite> select * from t1; > 1|2 > 3|4 > > sqlite> create index i1 on t1(a); > > sqlite> create view v1 as select a,b,a+b as f1 from t1; > sqlite> create view v2 as select a,b,a+b as f1 from t1 order by (a+b); > sqlite> create view v3 as select a,b,a+b as f1 from t1 order by f1; > sqlite> explain query plan select a,b,f1 from v2 where a=3 order by (f1); > 1|0|0|SCAN TABLE t1 (~1000000 rows) > 1|0|0|USE TEMP B-TREE FOR ORDER BY > 0|0|0|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (a=?) (~7 rows) > 0|0|0|USE TEMP B-TREE FOR ORDER BY > => Why is index i1 not used? > The query-flattener does not run because both the inner and outer query contain ORDER BY clauses. See http://www.sqlite.org/src/artifact/9b759521f8?ln=2724 and the surrounding context for additional information on the query flattener, its implementation, and the specific rule that is causing your problems. Perhaps we could add a new optimization: IF: (1) both inner and outer queries have an ORDER BY clause, and (2) the inner query omits both LIMIT and OFFSET THEN: drop the ORDER BY from the inner query Such an optimization would cause the index to be used in the case above and I cannot think of an instance where that optimization would lead to incorrect results. On the other hand, I might be overlooking some pathological case where this proposed optimization leads to an incorrect result. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
