On 2019/04/27 0:02, Tom Lane wrote: > Amit Langote <langote_amit...@lab.ntt.co.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
