Hi, My expectation was, that the actual used ORDER term is something like merge from outer to inner orders. But, this is wrong.
>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. Sounds not so bad. Can I add this "new optimization" or is this a bigger change? Mit freundlichen Grüßen / Best regards Maik Scholz CM-AI/PJ-CF42 Tel. +49 5121 49 5391 PC-Fax +49 711 811 505 5391 BeQIK -----Ursprüngliche Nachricht----- Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] Im Auftrag von Richard Hipp Gesendet: Mittwoch, 24. Oktober 2012 13:27 An: General Discussion of SQLite Database Cc: Klein Thomas (CM-AI/PJ-CF23); EXTERNAL Zeise Ralf (TCP, CM-AI/PJ-CF42); Erben Peter (CM-AI/PJ-CF42) Betreff: Re: [sqlite] Problem: Index not used with ORDER BY on view 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
