Re: findTargetlistEntrySQL92() and COLLATE clause

On 2019/04/27 0:02, Tom Lane wrote:
> 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?

Ah, OK. Thanks for the explanation.

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

In contrast to your example above, maybe the COLLATE case is less
ambiguous in terms of what ought to be done?

Thanks,
Amit