On Mon, May 6, 2013 at 9:39 PM, Keith Medcalf <kmedc...@dessus.com> wrote: >> I read this: >> >> SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; >> >> as saying "select all a from t1 that are not in t2, *then* order that >> by that a". > > No, that is not what it says, actually. The translation of your statement > into SQL Declarative would be:
Certainly if you look at SQL books and the SQLite docs you get the impression that the ordering term applies to the final result set. There's no point ordering, say, correlated sub-queries. Some RDBMSes seem to allow it, though I'm not sure what that means if anything outside the context of group_concat()-like aggregate functions -- perhaps it works only as a hint to the query planner that ordering an intermediate result set will net better performance? In any case, SQLite3 only allows ORDER BY at the very end of a select, not in sub-selects. And it applies the ORDER BY to the result set. It seems outrageous to think that a COLLATE clause on ORDER BY should be applied transitively to anything other than ordering the final set unless the result is guaranteed to be the same. At the very least it's weird and surprising. There *is* a point to ordering sets for aggregate function computation, because some aggregate functions are produce different results for the same input set in different orders. E.g., group_concat(). But this is a different story. Outside of such aggregate functions there's no point to ordering anything but the final result set. > select a from t1 where not exists (select * from t2 where a = t1.a) order by > a; You can rewrite EXCEPT that way. That doesn't mean that the first statement is not equivalent to this one; on the contrary, you can rewrite that way because the result is equivalent to the first. And this might well be logical proof that there's bug here! But I'm probably stretching something... Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
