Maybe an implicit ORDER BY random() ;) -----Urspr?ngliche Nachricht----- Von: Mohit Sindhwani [mailto:ml3p at onghu.com] Gesendet: Dienstag, 03. M?rz 2015 18:22 An: General Discussion of SQLite Database Betreff: Re: [sqlite] full table scan ignores PK sort order?
On 3/3/2015 6:59 PM, Jean-Christophe Deschamps wrote: > At 11:27 03/03/2015, you wrote: > ???? >> - the full table scan returns rows in rowID order, which is the order >> in which the rows were added to the table > `--- > > No and no. > > An SQL engine doesn't guarantee any row "order" unless you explicitely > force an ORDER BY clause. Think of row order as random, where rowid > order is just a possibility among zillions others. Of course neither > SQLite nor other engines willingly use random() to foil your > expectations but you should never rely on such an implementation detail. > > Also rowids are technically independant of insertion order: you may > feed any valid random literal rowids at insert time. > If it wasn't a performance issue, I wish that SQLite would sometimes actually return values that are not in the order of insertion or by rowid just so that people would learn this lesson earlier :) For sure, it took me a while... actually, till I read a book about SQlite. Cheers, Mohit. _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.