On 5/1/19, Tom Bassel <tbas...@pettymail.com> wrote:
> Hi,
>
> In this page in the docs: https://sqlite.org/queryplanner.html#searching
>
> it says:
> "The rows are logically stored in order of increasing rowid"
>
> Would this imply that executing a SELECT would always return the rows in
> order or increasing rowid?
>
> So that a "SELECT * from MyTable" would return all the rows in ROWID order
> because that is how they are stored.
That is the way the query planner currently works but there is NO
GUARANTEE that it will continue to work that way in the future. And,
in fact, if you set "PRAGMA reverse_unordered_selects=ON" it will not.
So you should never rely on that. Instead, write:
SELECT * FROM MyTable ORDER BY rowid;
If you EXPLAIN both statements:
EXPLAIN SELECT * FROM MyTable;
EXPLAIN SELECT * FROM MyTable ORDER BY rowid;
You will see that exactly the same sequence of bytecodes are produced.
So the run-time is identical. The difference is that the second form
is not prone to being broken the next time I enhance the query
planner.
Applications have broken in the past due to missing ORDER BY clauses.
Some people would do:
SELECT x FROM MyTable;
the application would rely on the "x" values coming out in ROWID
order. But then I enhanced the query planner to instead use an index
on "x" rather than the main table (since the Index likely is smaller
and thus requires less I/O) and suddenly the order changed because
they left off the "ORDER BY rowid" clause.
So if order is important to your output, *always* use an ORDER BY
clause. Always. No exceptions.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
