Date: Mon, 3 Oct 2016 16:25:09 +0200 From: Clemens Ladisch <[email protected]> To: [email protected] Subject: Re: [sqlite] Default ordering of SELECT query Message-ID: <[email protected]> Content-Type: text/plain; charset=utf-8
>>Andrii Motsok wrote: >> "If a SELECT statement that returns more than one row does not have an ORDER >> BY clause, the order in which the rows are returned is undefined" >> >> We have scenario: >> * database is opened in WAL mode >> * 10 rows have been read to container1 >> * database has been checkpointed >> * database has been closed >> * database has been opened on DELETE mode >> * 10 rows have been read to container2 >> >> Can we assume that container1 == container2 ? >This assumption happens to be true in the current version of SQLite, if no >other changes are being made to the database before re-opening it. >But to assume this is extremely dangerous, because you don't know how your >application will be changed in the future. Just use ORDER BY. >Regards, >Clemens Hi, 1) In case of ORDER BY if we choose ordering which does not follow "index ordering" we will get performance degradation. 2) If we use ORDER BY can we be sure that order of rows is the same for two subsequent calls (without any writes to database) for non UNIQUE index? 3) Just from curiosity - could you please provide any real world scenario which shows how two similar subsequent calls can return different order of rows? Regards, Andrii _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
