Re: findTargetlistEntrySQL92() and COLLATE clause - Mailing list pgsql-hackers

From Amit Langote
Subject Re: findTargetlistEntrySQL92() and COLLATE clause
Date
Msg-id da1c22ae-5652-e996-5303-3d3cf4540303@lab.ntt.co.jp
Whole thread Raw
In response to Re: findTargetlistEntrySQL92() and COLLATE clause  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
On 2019/04/27 0:02, Tom Lane wrote:
> Amit Langote <Langote_Amit_f8@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




pgsql-hackers by date:

Previous
From: Kyotaro HORIGUCHI
Date:
Subject: Re: standby recovery fails (tablespace related) (tentative patchand discussion)
Next
From: Heikki Linnakangas
Date:
Subject: Re: VACUUM can finish an interrupted nbtree page split -- is thatokay?