On Sun, May 15, 2016 at 10:02 PM, Darren Duncan <darren at darrenduncan.net> wrote: > 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. >
'SELECT first,second FROM members ORDER BY second LIMIT 3' (that's mysql format right?) I don't see a full set as a requirement (such that the output would be 2 or 4 records and not the 3 I asked for...) . the query implies 3 rows, not 3 sets. SELECT first,second FROM members ORDER BY second LIMIT 3,3 (for the next 3 lines I'm displaying on a form for instance) and specifying that the result set includes a first name, the result sets taken as a hole are not guaranteed equal (procedurally and in practice they may be, but pessimistically...). > > -- Darren Duncan > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
