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.
### Versions used
PostgreSQL 16.2 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
### Steps to reproduce
1.start `psql`
2.create two tables, having one element in common:
```sql
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');
```
3.execute a query, which references a column of the query in an order by with a collation:
```sql
SELECT
BAR.descr as "descr"
from BAR left join FOO on BAR.foo_id = FOO.id
order by descr COLLATE "en-x-icu" ASC;
```
4.observe the error message:
```log
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 works if we omit the "COLLATE" clause. I'd expect the lookup to work the same way with the "COLLATE" clause.