Re: Seemingly inconsistent ORDER BY behavior

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