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

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