From: Tom Lane <tgl@sss.pgh.pa.us>
To: ivningman@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18542: Order by expression,
 that contains column from projection isn't working
In-reply-to: <18542-adf5273ccfea5558@postgresql.org>
References: <18542-adf5273ccfea5558@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 16 Jul 2024 09:12:36 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3319081.1721133915.1@sss.pgh.pa.us>
Date: Tue, 16 Jul 2024 08:45:15 -0400
Message-ID: <3319082.1721133915@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3319082.1721133915%40sss.pgh.pa.us>
Precedence: bulk

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