From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Bender, Patrice <patrice.bender@sap.com>
To: 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 20:18:02 +0100
Message-ID: <8cfcd0ff0ca3c29c4cbe56a5648c014913db1c3a.camel@cybertec.at> (raw)
In-Reply-To: <AM9PR02MB6564A376D53C93635B6886AF9B362@AM9PR02MB6564.eurprd02.prod.outlook.com>
References: <AM9PR02MB6564A376D53C93635B6886AF9B362@AM9PR02MB6564.eurprd02.prod.outlook.com>
On Mon, 2024-03-25 at 15:52 +0000, Bender, Patrice wrote:
> PostgreSQL16.2
> on aarch64-unknown-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014)13.2.120231014,64-bit
>
> CREATE TABLE Foo (id SERIAL PRIMARY KEY, descr TEXT);
> CREATE TABLE Bar (id SERIAL PRIMARY KEY, descr TEXT, foo_id INT);
> -- optionally, insert some data
> INSERT INTO BAR (DESCR, FOO_ID) VALUES ('Description for Bar 1', 1);
> INSERT INTO FOO (DESCR) VALUES ('Description for Foo 1');
>
> SELECT
> BAR.descr as "descr"
> from BAR left join FOO on BAR.foo_id = FOO.id
> order by descr COLLATE "en-x-icu" ASC;
>
> ERROR: column reference "descr" is ambiguous
> LINE 1: ...BAR left join FOO on BAR.foo_id = FOO.id order by descr COLL...
>
> 5.If you remove the collate, the query will work.
>
> ### Expected behavior
>
> In the ANSI sql
> standard, the reference in the order by should first be looked up in the queries columns, and
> then
> in the tables.
> This worksif we omit the "COLLATE" clause. I'd expect the lookup to work the same way with the "COLLATE" clause.
This is not a bug.
If you say "ORDER BY descr", you are referencing the result set column with that alias.
If you say "ORDER BY descr COLLATE "en-x-icu"" or "ORDER BY descr || ' '" or anything else
that is not a plain column reference, but an expression, "descr" is *not* understood to
be a result set column, but a column of one of the involved tables, and that reference
is ambiguous.
There is little sense in quoting the SQL standard here, because as far as I can tell it
only supports column names, no expressions, in the ORDER BY clause.
Yours,
Laurenz Albe
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: laurenz.albe@cybertec.at, 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: <8cfcd0ff0ca3c29c4cbe56a5648c014913db1c3a.camel@cybertec.at>
* 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