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. Whether returning above or below the limit is done, is a separate thing to decide, though I suggest returning above is better. -- Darren Duncan
