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