Thread: findTargetlistEntrySQL92() and COLLATE clause

Hi,

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";
                                                 ^
I expected this to resolve the output column number (2) to actual column
(b) and apply COLLATE clause on top of it.  Attached patch makes it so by
teaching findTargetlistEntrySQL92() to recognize such ORDER BY items and
handle them likewise.  With the patch:

select a::text, b from foo order by 1, 2 collate "C";
 a  │    b
────┼──────────
 ab │ ab wins
 ab │ ab1 wins
 ab │ ab2 wins
(3 rows)

select a::text, b from foo order by 1 collate "C", 2;
 a  │    b
────┼──────────
 ab │ ab1 wins
 ab │ ab2 wins
 ab │ ab wins
(3 rows)

select a::text, b from foo order by 3 collate "C", 2;
ERROR:  ORDER BY position 3 is not in select list
LINE 1: select a::text, b from foo order by 3 collate "C", 2;

Am I missing something?

Thanks,
Amit

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?

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

            regards, tom lane

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