On 2016-05-15 11:17 PM, dandl 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 >>> >> 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. > > I would say that this is an invalid query. As already applies for DISTINCT > and GROUP BY, the query parser should require that every column in the > column list should appear in the ORDER BY list. If it does not, then the > result is indeterminate. > > Sqlite already permits indeterminate queries, but other SQL engines do not.
Not EVERY column, just a superkey of the result columns should suffice. -- Darren Duncan
