Re: Why lower's not accept an AS declaration ?

On Mon, 18 Aug 2003, Tom Lane wrote:

> =?iso-8859-15?q?Herv=E9=20Piedvache?= <herve(at)elma(dot)fr> writes:
> > Is it an example more realistic for you to make an order by lower of
> > something as an alias ?
>
> Aliases attached to SELECT output columns are visible outside the
> SELECT, not inside it. The special case for ORDER BY simple-column-name
> is a kluge for compatibility with a now-obsolete version of the SQL spec
> (SQL92 expects this to work, SQL99 doesn't) and we aren't going to
> extend it. See past discussions in the archives (I seem to recall
> answering this same question within the past week...)

Actually, rereading SQL99, I wonder if it would expect this to work.

Using 14.1's wording on order by clauses, syntax rule 18, h
"Ki is a <value expression>... shall contain a <column reference>.
i) Let X be any column reference directly contained in Ki.
ii) If X does not contain an explicit <table or query name> or
<correlation name>, then Ki shall be a <column name> that shall
be equivalent to the name of exactly one column of ST."

T is the result of evaluating the query expression. If no sort key refers
to a column that isn't a column of T then ST is the same as T.

If the result of evaluating the query expression (T) is the output of the
query expression with the output column names then it should allow output
column names in the value expressions of the order by clause I believe.