Thread: BUG #18542: Order by expression, that contains column from projection isn't working

The following bug has been logged on the website:

Bug reference:      18542
Logged by:          Aleksandr Novozhilov
Email address:      ivningman@gmail.com
PostgreSQL version: 16.1
Operating system:   Windows
Description:

select z as y from
 (select 1 as z) x
order by y + 1
That query finishes with error: 
ERROR: column "y" does not exist
  Position: 48
But wrapping it with select works fine:
select * from (select z as y from
 (select 1 as z) x) c
order by y + 1

PG Bug reporting form <noreply@postgresql.org> writes:
> select z as y from
>  (select 1 as z) x
> order by y + 1
> That query finishes with error: 
> ERROR: column "y" does not exist
>   Position: 48
> But wrapping it with select works fine:
> select * from (select z as y from
>  (select 1 as z) x) c
> order by y + 1

This is not a bug.  See

https://www.postgresql.org/docs/current/queries-order.html

particularly "Note that an output column name has to stand alone, that
is, it cannot be used in an expression".  The fact that we allow this
at all is a hangover from SQL92; per SQL99 and later, variables in
ORDER BY should refer to input columns (that is, outputs of the FROM
clause).

            regards, tom lane