On Sun, May 15, 2016 at 9:29 PM, Darren Duncan <darren at darrenduncan.net> wrote: > On 2016-05-15 12:35 AM, Simon Slavin wrote: >> >> On 15 May 2016, at 6:04am, Darren Duncan <darren at darrenduncan.net> wrote: >> >>> You seem to be forgetting the fact that LIMIT/OFFSET is not its own >>> clause, rather it is an extension to the ORDER BY clause and only has >>> meaning within the context of the ORDER BY it is part of. >> >> >> All true. But it brings up a question. Suppose the following: >> >> first second >> ----- ------ >> Mark Spark >> Emily Spark >> Mary Soper >> Brian Soper >> >> SELECT first,second FROM members ORDER BY second LIMIT 3 >> >> Without looking up either a standards document for SQL or the >> documentation for your favourite implementations of SQL, answer this >> question: >> >> Does the documentation for your favourite implementation of SQL state that >> you'll get the same rows every time you execute the above "SELECT" ? > > > I think a proper solution for this then is to treat the LIMIT as approximate > rather than exact; it indicates a desire rather than a promise. > > In the scenario you describe, the query should return either 2 rows or 4 > rows, so that ALL of the rows whose second field value of "Spark" are, or > are not, returned. Projecting this to there not being an ORDER BY clause, > either all rows are returned or zero rows are returned. Thus the result is > deterministic.
even if it did for 'spark' rows (which either case you suggest would be horrible) 'soper' would still be non-deterministic, and rebuilding indexes could reorder the results. > > Whether returning above or below the limit is done, is a separate thing to > decide, though I suggest returning above is better. > > -- Darren Duncan > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
