From: Tom Lane <tgl@sss.pgh.pa.us>
To: depesz@depesz.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: order by alias - doesn't work sometimes?
In-reply-to: <aO5jUGlQgBBAOp4_@depesz.com>
References: <aO5jUGlQgBBAOp4_@depesz.com>
Comments: In-reply-to hubert depesz lubaczewski <depesz@depesz.com>
	message dated "Tue, 14 Oct 2025 16:50:56 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <959906.1760454631.1@sss.pgh.pa.us>
Date: Tue, 14 Oct 2025 11:10:31 -0400
Message-ID: <959907.1760454631@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/959907.1760454631%40sss.pgh.pa.us>
Precedence: bulk

hubert depesz lubaczewski <depesz@depesz.com> writes:
> But:
> =$ select unnest( ARRAY['d', 'c', 'a'] ) x order by x <> 'a';
> ERROR:  column "x" does not exist
> LINE 1: select unnest( ARRAY['d', 'c', 'a'] ) x order by x <> 'a';
>                                                          ^

> Obviously I can put the unnest() call in from, and then it works, but
> I'm curious, why we can `order by x`, but not `order by x <> 'a'` ?

ORDER BY output-column-alias is a messy hangover from SQL92.
In later SQL versions, identifiers in ORDER BY have the same
interpretation as in WHERE and other clauses, namely they are
columns sourced by the FROM clause.  Supporting both interpretations
makes for a lot of ambiguity, so we only allow the old interpretation
in exactly the case required by SQL92, namely "ORDER BY identifier".

This is documented ...

			regards, tom lane