(quoting fixed) Scholz Maik (CM-AI/PJ-CF42) wrote: > Richard Hipp wrote: > > 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. > > My expectation was, that the actual used ORDER term is something like merge > from outer to inner orders.
If the inner and outer clauses are on different fields, and if one relies on the (undocumented!) fact that SQLite's sorting is stable, then the order of the result indeed is a combination of both. However, this is guaranteed neither by the SQL standards nor by SQLite, and many other database engines can use non-stable sorting algorithms. This new optimization could change some queries, but only in allowed ways, and the recent covering-index optimizations introduced similar changes anyway. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
