findTargetlistEntrySQL92() and COLLATE clause

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: findTargetlistEntrySQL92() and COLLATE clause
Date: 2019-04-26 03:56:44
Message-ID: 2173dc58-6697-1e10-9604-a7c9f2a8bb55@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
findTargetlistEntrySQL92-COLLATE-1.patch text/plain 1.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2019-04-26 04:20:31 Re: pgsql: Allow insert and update tuple routing and COPY for foreign table
Previous Message Iwata, Aya 2019-04-26 03:55:30 RE: libpq debug log