On 6 Oct 2016, at 1:41pm, Andrii Motsok <andrii.mot...@tomtom.com> wrote:
>>>> How do you mean "follow index ordering"? > This is only my observation that sqlite usually returns rows in the order in > which they are sorted in by index. So if I have two indexes and SELECT with > WHERE followed by AND choice of indexes can be different. This choice depends > on query planner and statistics. So there is always a chance that ORDER BY > will require additional sorting. SQLite indexes work like this: every UNIQUE index (and primary key indices must, of course, be unique) ends in the table's primary key. Even if did not specify it yourself, SQLite has added it invisibly and those primary key values are stored with the other values for each index entry. So even a short index, on a table with a long primary key, means that the index takes up lots of filespace. This has the effect you've noticed on anything with an ORDER BY clause: when SQLite decides to use an index to handle the clause, it automatically gets the primary key at the end of the index, and this governs the order in which rows are returned. So yes, assuming an uncorrupt unchanging set of rows, assuming the same WHERE and ORDER BY clauses, and assuming nothing happens to change the query plan (as ANALYZE might) the same SELECT will always return them in the same order. But this is still not documented as a requirement for SQL so it's still not a good idea to depend on it if you don't have to. It may change in SQLite 4 or perhaps even before then. There is one exception to the above which was created precisely to debug this point: "PRAGMA reverse_unordered_selects = ON". This is used specifically to test whether a programmer has made the above assumption and to warn them to fix their code. And it can be done by any thread/process which has access to your database connection. >>>> Why not simply Order by your non-unique field AND then by row_id > Unfortunately this is not simple. We don't have row_id. I am the library on > top of sqlite which accepts and executes SQL queries. I cannot force my users > to add ORDER BY to all queries especially for non UNIQUE indexes. And I need > to do some work under cover (checkpoint and reopen in DELETE mode) and then I > need to restore queries statues to their previous position. Yep. If it doesn't work there's nothing you can do about it. It might be possible to parse all ORDER BY clauses passed to your library and add ",rowid" to the end of each one. But I have no idea if that would be compatible with the way your library works. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
