On 3 Mar 2015, at 10:59am, Jean-Christophe Deschamps <jcd at antichoc.net> wrote:
> 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. Just to formalise this ... SQL defines a table as a set of rows. There is no order to a set: it's just a jumble of things like Scrabble tiles in a bag. You can't tell what order rows were added in. The most frequent error SQL users make is to assume that table rows are inherently ordered in primary key order. They're not. The primary key is just another unique index. To help users avoid incorrect assumptions about an inherent 'order of rows' SQLite provides PRAGMA reverse_unordered_selects = ON which can be useful for testing code which was hacked up in a hurry. Simon.
