Re: findTargetlistEntrySQL92() and COLLATE clause

Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> writes:
> I couldn't find old discussions or source code comments about this, but
> has someone encountered the following error and wondered whether it's
> working that way for a reason?

> select a::text, b from foo order by 1, 2 collate "C";
> ERROR: collations are not supported by type integer
> LINE 1: select a::text, b from foo order by 1, 2 collate "C";
> ^

The reason it works that way is that *anything* except a bare integer
constant is treated according to SQL99 rules (that is, it's an ordinary
expression) not SQL92 rules. I do not think we should get into weird
bastard combinations of SQL92 and SQL99 rules, because once you do,
there is no principled way to decide what anything means. Who's to say
whether "ORDER BY 1 + 2" means to take column 1 and add 2 to it and then
sort, or maybe to add columns 1 and 2 and sort on the sum, or whatever?

IOW, -1 on treating COLLATE as different from other sorts of expressions
here. There's no principle that can justify that.

regards, tom lane