Обсуждение: BUG #4030: Inconsistency: ORDER BY clauses involving column aliases

The following bug has been logged online:

Bug reference:      4030
Logged by:          Dave Gurnell
Email address:      d.j.gurnell@gmail.com
PostgreSQL version: 8.2.5
Operating system:   Mac OS X / Debian
Description:        Inconsistency: ORDER BY clauses involving column aliases
Details:

Hi,

I've noticed a possible inconsistency in the way PostgreSQL 8.2.5 renders
ORDER BY clauses.

Here is an example:

Create a table:

    create table numbers (a integer, b integer, c integer);

Run a query ordered on aliases of its columns:

    select x.a as xa, x.b as xb, x.c as xc from numbers as x order by xa,
xb, xc;

     xa | xb | xc
    ----+----+----
    (0 rows)

Run a query ordered on an expression involving these aliases:

    test=# select x.a as xa, x.b as xb, x.c as xc from numbers as x order by
(xa + xb + xc);
    ERROR:  column "xa" does not exist
    LINE 1: ... x.b as xb, x.c as xc from numbers as x order by (xa + xb +
...

Is this expected behaviour or is it a bug?

Many thanks,

-- Dave

"Dave Gurnell" <d.j.gurnell@gmail.com> writes:
> Run a query ordered on an expression involving these aliases:

>     test=# select x.a as xa, x.b as xb, x.c as xc from numbers as x order by
> (xa + xb + xc);
>     ERROR:  column "xa" does not exist
>     LINE 1: ... x.b as xb, x.c as xc from numbers as x order by (xa + xb +
> ...

> Is this expected behaviour or is it a bug?

It's expected and documented.  No, it isn't very consistent :-(.
We're trying to support both the SQL92 and SQL99 approaches to ORDER BY,
and they're not the same.

            regards, tom lane