> 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: select a from t1 where not exists (select * from t2 where a = t1.a) order by a; which, when a collation is applied in the order by, applies only to the ordering of the result set and not to the computation of the result set. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Nico Williams > Sent: Monday, 06 May, 2013 19:36 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Suspected bug in SELECT EXCEPT ORDER COLLATE > > On Mon, May 6, 2013 at 4:22 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > It is difficult to determine what is correct behaviour. I would think > that the "order by" clause applies to the set operation, therefore any > collation being applied has to be applied also to the component sets > before the operation is performed. This implies the current operation is > correct although it may lead to, perhaps, non-intuitive results. > > 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". > > I read this: > > SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; > > to mean "select a from t1 that are not in t2, *then* order that by a > with the nocase collation". > > I don't understand why the COLLATE clause on the ordering term should > be applied transitively to the sub-expressions in the select instead > of only to the result set of the select. It makes no sense, > intuitively, and smells like a bug. But what does the standard say? > > Note that there's a workaround: > > SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2) ORDER BY > a COLLATE nocase; > > Nico > -- > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
