On 2016-05-15 9:56 PM, J Decker wrote: > 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: >>> >>> 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.
No, it is still deterministic. The ORDER BY clause specified a partial order of the results, not a total order. What I specified returns only complete groups of rows where within each group the rows are unordered but the groups as a whole are ordered relative to each other. The fact this is deterministic would probably be more clear if the result rows were nested, one outer row per "group" that I mentioned. But even if not, the caller knew that they were only ordering by second but selecting first, so if they see multiple rows with the same second value, they know that those rows are not sorted between themselves, only that rows with different second values are sorted relative to each other. So fully deterministic. -- Darren Duncan
