Thread: BUG #1265: sorting by (ORDER BY) result of an operation for names assigned by AS does not work

The following bug has been logged online:

Bug reference:      1265
Logged by:          Ansis

Email address:      ataols@latnet.lv

PostgreSQL version: 7.4.2

Operating system:   Gentoo Linux

Description:        sorting by (ORDER BY) result of an operation for names
assigned by AS does not work

Details:

 The query:
SELECT id, lang as name, (SELECT lang FROM participants WHERE id =
event.participant) as pname FROM event ORDER BY name || pname;
 returns an error:
ERROR: column "name" does not exist

 However, "ORDER BY name" works, "ORDER BY name, pname" works etc. Also, if
I use original column names, not assigned ones, it works with cncatenation
operator too. So, the error occurs only then assigned names are used inside
operator - the Postgre does not find this name.
 It looks like a bug - and an easy fixable one.

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
>  The query:
> SELECT id, lang as name, (SELECT lang FROM participants WHERE id =
> event.participant) as pname FROM event ORDER BY name || pname;
>  returns an error:
> ERROR: column "name" does not exist

It's not a bug.  Per
http://www.postgresql.org/docs/7.4/static/sql-select.html
under "ORDER BY Clause"

: 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.

The ability to reference an output column in ORDER BY is actually a legacy
feature that was removed in the SQL99 spec.

            regards, tom lane