Re: Unexpected Name Resolution Behavior with ORDER BY COLLATE Clause in PostgreSQL 16.2

"Bender, Patrice" <patrice(dot)bender(at)sap(dot)com> writes:
> In PostgreSQL 16.2 running on aarch64-unknown-linux-musl, an
> unexpected error occurs when executing a SQL query that orders the
> results by a column with a specified collation (using the COLLATE
> clause) after performing a join. Specifically, when attempting to
> order the results by a column that exists in both tables involved in
> the join, the query fails with an "ambiguous column reference" error
> if the ORDER BY clause includes a COLLATE specification. This
> behavior diverges from the expected and standard SQL behavior, where
> the column reference in the ORDER BY clause should first resolve to
> the columns listed in the SELECT statement before considering the
> source tables.

This behavior appears to me to be consistent with our documentation
[1]:

Each expression can be the name or ordinal number of an output
column (SELECT list item), or it can be an arbitrary expression
formed from input-column values.
...
If an ORDER BY expression is a simple name that matches both an
output column name and an input column name, ORDER BY will
interpret it as the output column name. This is the opposite of
the choice that GROUP BY will make in the same situation. This
inconsistency is made to be compatible with the SQL standard.

As soon as you add COLLATE, it's not a simple name anymore but an
expression, and so it is not considered to possibly match an output
column name.

As for the question of SQL standard compliance, please cite chapter
and verse supporting your position. I believe that the spec changed
between SQL92 and SQL99. The SQL92 standard said that ORDER BY
items could be output column names or numbers (and nothing else).
Later editions say that ORDER BY expressions are just like any
others, ie names in them are *input* column names. Our interpretation
is a bit of a mess but is intended to preserve some compatibility
with SQL92, while following the modern spec in any case that isn't
strictly SQL92 compliant. (I suppose the para quoted above should
be modified to explain that a little more clearly. The compatibility
section at the bottom of the page has the details, but this bit is
kind of misleading by itself.)

regards, tom lane

[1] https://www.postgresql.org/docs/current/sql-select.html#SQL-ORDERBY