order by alias - doesn't work sometimes?
Hi,
It very well might not be a bug, but it caught me by surprise.
Tested on Pg 19 from git head:
=$ select unnest( ARRAY['d', 'c', 'a'] ) x order by x;
x
───
a
c
d
(3 rows)
Works, and shows that I can order by alias name of column that is generated by srf.
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'` ?
Best regards,
depesz
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
On Tue, Oct 14, 2025 at 11:10:31AM -0400, Tom Lane wrote:
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 ...
Well, now I understand this:
Each expression can be the name or ordinal number of an output column
(SELECT list item), or it can be an arbitrary expression formed from
input-column values.
better. Thanks.
Best regards,
depesz