On 2016/05/16 8:17 AM, dandl wrote: >> 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.
I don't think any DB in existence forces you to specify every column (in the column list) also in the ORDER BY list? If you call this indeterminate, then you cannot say that "other SQL engines" do not allow indeterminate queries. They all do. What they don't allow is GROUP BY queries without aggregate specifiers on columns not included in the groupings. I also think that while SQLite allows this, it's not an oversight, there is an implicit method to it which may not be deterministic per se, but it is the Query maker's choice. Sometimes you don't care about the determinism - much like sometimes you don't care about the rest of the results when you say LIMIT 5. As Simon pointed out - ORDER BY and LIMIT etc already allows us to ask things that are not deterministic (in any DB) but not every result needs to be deterministic. If the question is: "Name 5 marine animals", nobody cares which 5 you name, or in which order you name them. It always seemed strange to me that in other DB engines when I KNOW the values in column 3 of the query is always going to be "20" in the grouping that I group by (or even when it doesn't matter what that value is), I can't even get my query to run in MSSQL if I don't go put that stupid column also in a MAX() function or remove it - so it forces me to do a most useless bit of extra coding so it itself can sleep well at night reveling in its own determinism. It's like a car preventing you to steer out of your lane when you haven't switched on your indicator... An accident waiting to happen. Note: I understand not all drivers are equal, and determinism in DBs is the safer choice, and I agree with it (And yes, I'm still one of the proponents of adding the "strict" mode in SQLite - please devs) - I just find it refreshing that some allow me to express what I really want in stead of making me type functions that has no value, and I can't agree calling such a feature "wrongful". Especially if the non-determinism of it is well-documented. Most of the outcries I've seen on this forum to do with the above (or the ducky typing etc.) comes from people who jumps from using some other DB server to SQLite and didn't read the documentation, and then be bitten by it not doing things as they expect. They then proceed to offer many reasons why they should be justified in "expecting" the DB to do whatever they expected (with some merit, I should add), mostly citing another big DB engine's manners. All this in lieu of just reading the SQLite manual. If they not going to read the docs, then it doesn't really matter which behaviour is chosen, it will surprise someone. </rant> Cheers, Ryan
