Thread: doc question about column name alias

Hi,
    I'm translating 7.1.2's doc to Chinese, and got one question while
doing these,
in queries.sgml line 795:

SELECT a AS b FROM table1 ORDER BY a;

I think it should be:

SELECT a AS b FROM table1 ORDER BY b;

but from the context:"   References to column names in the FROM clause
that are renamed in the select list are also allowed:"
it seems the original is correct. Am I misunderstood? How the SQL
standard saids?
Thanks.

    regards    laser

Weiping He <laser@zhengmai.com.cn> writes:
> SELECT a AS b FROM table1 ORDER BY a;
> I think it should be:
> SELECT a AS b FROM table1 ORDER BY b;

You are correct that the latter is the SQL-standard version.  The former
is *also* accepted by Postgres --- but the point the text is trying to
make is that "ORDER BY a" is interpreted as a reference to the original
column "a", not to the output expression that happens to also reference
"a".  Perhaps this equivalent situation is a little more clear:

    SELECT a+b AS c FROM table1 ORDER BY a+b;
    SELECT a+b AS c FROM table1 ORDER BY c;

Both of these are accepted by Postgres; only the second one is SQL92;
the first one could cause a+b to be computed twice per row, the second
only once per row.  (I think that in fact the system will notice that
it has duplicate subexpressions here, but that's an optimization.)

Feel free to propose improved wording for the English docs ...

            regards, tom lane

> Hi,
>     I'm translating 7.1.2's doc to Chinese, and got one question while
> doing these,
> in queries.sgml line 795:
>
> SELECT a AS b FROM table1 ORDER BY a;
>
> I think it should be:
>
> SELECT a AS b FROM table1 ORDER BY b;
>
> but from the context:"   References to column names in the FROM clause
> that are renamed in the select list are also allowed:"
> it seems the original is correct. Am I misunderstood? How the SQL
> standard saids?
> Thanks.

Both work:

    test=> select relname as x from pg_class order by relname;

and

    test=> select relname as x from pg_class order by x;

I am not sure which is more standards-compliant.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tom Lane <tgl@sss.pgh.pa.us> writes:

>> SELECT a AS b FROM table1 ORDER BY a;
>> I think it should be:
>> SELECT a AS b FROM table1 ORDER BY b;
>
> You are correct that the latter is the SQL-standard version.

In fact, this becomes pretty obvious when you consider the theoretical
ordering of the evaluation of the various parts of the statement.  The
ORDER BY is executed *after* the SELECT part, so it only knows about
the column name 'b', and doesn't know what 'a' refers to.  Actually,
ORDER BY isn't even part of the SELECT statement, really, but hides an
implicit CURSOR operation taking place during data output.  Allowing
the first version to work is of questionable value, since it might
cause errors and confusion when code is modified.  Consider the case
where you have "SELECT a AS b, b AS a ...".  I've done it!  :-)

The order of evaluation is: FROM, WHERE, GROUP BY, HAVING, SELECT,
and, finally, ORDER BY.

-tih
--
The basic difference is this: hackers build things, crackers break them.

Tom Ivar Helbekkmo wrote:

> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> >> SELECT a AS b FROM table1 ORDER BY a;
> >> I think it should be:
> >> SELECT a AS b FROM table1 ORDER BY b;
> >
> > You are correct that the latter is the SQL-standard version.
>
> In fact, this becomes pretty obvious when you consider the theoretical
> ordering of the evaluation of the various parts of the statement.  The
> ORDER BY is executed *after* the SELECT part, so it only knows about
> the column name 'b', and doesn't know what 'a' refers to.  Actually,
> ORDER BY isn't even part of the SELECT statement, really, but hides an
> implicit CURSOR operation taking place during data output.  Allowing
> the first version to work is of questionable value, since it might
> cause errors and confusion when code is modified.  Consider the case
> where you have "SELECT a AS b, b AS a ...".  I've done it!  :-)
>
> The order of evaluation is: FROM, WHERE, GROUP BY, HAVING, SELECT,
> and, finally, ORDER BY.

Thanks all for these info. & examples, they help me much to clearify
the question, the present wording is a little bit hard to understand, but
accurate and
efficent, and I think it's better to add a few lines to make it clear, like

"...but the SQL standard require (or "only permit") us to do:
select a as b from table1 order by b;"

    regards    laser