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
