From: Tom Lane <tgl@sss.pgh.pa.us>
To: Branko Radovanovic <branko.radovanovic.zg@gmail.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        Vik Fearing <vik@postgresfriends.org>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17853: COLLATE does not work with numeric column references
 in ORDER BY
In-reply-to: 
 <CAH70sp7SLk-UgdQyNToggCkJRRycKXSyaiNUQEx7gxocTPedPQ@mail.gmail.com>
References: <17853-57fa4c939f613eb6@postgresql.org>
 <3b32d6df-c6a4-c3fa-0eef-d4e2a8789fb6@postgresfriends.org>
 <CAKFQuwZwrTDx0=GwETi21BKjhioeWT=xe9Xm-8+79gFBOAOzRQ@mail.gmail.com>
 <CAKFQuwb6Lt7o1E7FPn2Je6t=obisv=mW6bPUTQgOvcGjXaFqQA@mail.gmail.com>
 <CAH70sp7SLk-UgdQyNToggCkJRRycKXSyaiNUQEx7gxocTPedPQ@mail.gmail.com>
Comments: In-reply-to Branko Radovanovic <branko.radovanovic.zg@gmail.com>
	message dated "Sun, 19 Mar 2023 10:34:46 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <744630.1679252080.1@sss.pgh.pa.us>
Date: Sun, 19 Mar 2023 14:54:40 -0400
Message-ID: <744631.1679252080@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/744631.1679252080%40sss.pgh.pa.us>
Precedence: bulk

Branko Radovanovic <branko.radovanovic.zg@gmail.com> writes:
> COLLATE is a good place to de-support numeric references because ORDER BY 1
> COLLATE "C" is then unambiguously an error, rather than a silent fail.

Yup.

> ...so in the grand scheme of things it doesn't really help. Sometimes it's
> actually easier to implement something than explain (in the doc) how or why
> it doesn't work (as expected or at all).

It's not that any specific choice of semantics would be hard to
implement.  It's that the syntax is fundamentally ambiguous, so if
you get too aggressive about saying "we'll resolve ambiguous cases
like this" then you risk queries silently doing something other than
what the user expected, giving rise to a different set of bug reports.

The back story here is that SQL92 said "the argument of ORDER BY is
an output column name or number", but SQL99 reversed course and said
"the argument of ORDER BY is an expression over the input columns".
So "ORDER BY 1" means two completely different things depending on
which spec version you read.  We didn't (and still don't) want to give
up compatibility with SQL92's way, for backwards-compatibility reasons
and because it's such a handy shortcut in many cases.  But we use the
SQL92 interpretation only when the clause satisfies SQL92 exactly.
"COLLATE" wasn't in SQL92; so if we did what you claim we should do
we would be applying SQL92 semantics to a query that isn't even legal
SQL92 syntax, while failing to comply with SQL99 for a query that *is*
legal SQL99.

I like throwing an error better than either of those choices, so
I'm perfectly satisfied with the code's behavior as-is.  What seems
open for debate is whether the documentation needs to address this
case specifically.  I think it's already sufficiently implied by [1],
but perhaps it isn't.

			regards, tom lane

[1] https://www.postgresql.org/docs/current/queries-order.html