From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bender, Patrice <patrice.bender@sap.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: den Os, Bob <bob.den.os@sap.com>
Subject: Re: Unexpected Name Resolution Behavior with ORDER BY COLLATE Clause in PostgreSQL 16.2
Date: Mon, 25 Mar 2024 15:04:15 -0400
Message-ID: <2528106.1711393455@sss.pgh.pa.us> (raw)
In-Reply-To: <AM9PR02MB6564A376D53C93635B6886AF9B362@AM9PR02MB6564.eurprd02.prod.outlook.com>
References: <AM9PR02MB6564A376D53C93635B6886AF9B362@AM9PR02MB6564.eurprd02.prod.outlook.com>
"Bender, Patrice" <patrice.bender@sap.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, patrice.bender@sap.com, pgsql-bugs@lists.postgresql.org, bob.den.os@sap.com
Subject: Re: Unexpected Name Resolution Behavior with ORDER BY COLLATE Clause in PostgreSQL 16.2
In-Reply-To: <2528106.1711393455@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox