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
