Richard Hipp <drh@sqlite.org> writes:
> Just to be clear, you intend that a COLLATE clause in the ORDER BY is
> treated as an expression, right?
Yeah, that is the current behavior, although I think that it probably fell
out rather than being consciously thought about. Arguably it's the wrong
thing, because it looks to me like SQL92 allowed
select ... order by 1 collate "something"
which our current code will interpret as ordering by a constant (and then
complain that you can't collate an int...). Don't know if it's worth
changing --- we have our own backwards-compatibility concerns to think
about, and there haven't been any field complaints about this. I doubt
there's any pressure at this point to become more compatible with SQL92,
as opposed to more compatible with later editions.
regards, tom lane