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.
