Re: Seemingly inconsistent ORDER BY behavior

Richard Hipp <drh(at)sqlite(dot)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