A more complete implementation of fullscan-by-covering-index can be seen here:
http://www.sqlite.org/src/info/cfaa7bc128 Many of the changes were the addition of an "ORDER BY rowid" clause to test queries in the test suite. For the previous 12 years, SQLite has always returned content in rowid order if there was no ORDER BY clause and no index could be used. But with the fullscan-by-covering-index change, that is no longer the case. The "ORDER BY rowid" clause does not actually force a sort, but it does disable the use of a covering index for the scan. If the fullscan-by-covering-index change does go into the trunk, I fear it will break many applications, because I believe many SQLite applications are probably making the assumption that unindexed queries always return their results in rowid order. The documentation clearly states that this is not necessarily the case, but who ever reads the documentation. We have also, for years, provided the reverse_unordered_selects pragma (see http://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects) which an application can used to locate places where there are unwarranted assumptions about the order of the results. But I doubt many applications actually use this pragma. You can well argument that applications that depend on the order of results when there is no ORDER BY clause are already broken, and that this change merely exposes their brokenness. I won't dispute that. Nevertheless, with this changes, those applications will stop working. So my thinking now is that this optimization should not be merged to trunk unless it is first disabled by default and only enabled by a compile-time or start-time option. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
