pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedorder by alias - doesn't work sometimes?
3+ messages / 2 participants
[nested] [flat]
* order by alias - doesn't work sometimes?
@ 2025-10-14 14:50 hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 3+ messages in thread
From: hubert depesz lubaczewski @ 2025-10-14 14:50 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: order by alias - doesn't work sometimes?
@ 2025-10-14 15:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2025-10-14 15:10 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: order by alias - doesn't work sometimes?
@ 2025-10-14 15:46 hubert depesz lubaczewski <depesz@depesz.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: hubert depesz lubaczewski @ 2025-10-14 15:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-10-14 15:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-14 14:50 order by alias - doesn't work sometimes? hubert depesz lubaczewski <depesz@depesz.com>
2025-10-14 15:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-14 15:46 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox