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

On Tue, 19 Aug 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > Yeah. These rules don't seem to make much sense, why bother making
> > it a general value expression if you're going to limit it like that?
> > It almost makes me wonder what SQL200x is going to do to the clause.
>
> Maybe it's a typo ... could someone check the SQL200x wording?

It didn't appear to be too recent, but I found a link to what appears to
be an older draft, and the wording in that section seemed to have changed
again. Maybe some of the other rules moved out to other sections, I
didn't go through it for that.

--------------------------------------------------------------

18) If an <order by clause> is specified, then:

a) Let OBC be the <order by clause>. Let NSK be the number of <sort
specification>s in OBC. For each i between 1 (one) and NSK, let K i be the
<sort key> contained in the i-th <sort specification> in OBC.

b) Let DT be the declared type of K i .

c) Each K i shall contain a <column reference> and shall not contain a
<subquery> or a <set function specification>.

d) If QE is a <query expression body> that is a <non-join query
expression> that is a <non-join query term> that is a <non-join query
primary> that is a <simple table> that is a <query specification>, then
the <cursor specification> is said to be a simple table query.

e) Case:

i) If <sort specification list> contains any <sort key> K i that contains
a column reference to a column that is not a column of T, then:

1) The <cursor specification> shall be a simple table query.

2) Let TE be the <table expression> immediately contained in the <query
specification> QS contained in QE.

3) Let SL be the <select list> of QS. Let SLT be obtained from SL by
replacing each <column reference> with its fully qualified equivalent
(that is, including either an explicit <correlation name> or an explicit
<table name> including either the keyword MODULE or an explicit <catalog
name> and an explicit <unqualified schema name>).

4) Let OBCT be obtained from OBC by replacing each <column reference>
that references a column of TE with its fully qualified equivalent (that
is, including either an explicit <corrrelation name> or an explicit <table
name> including either the key-word MODULE or an explicit <catalog name>
and an explicit <unqualified schema name>).

5) For each i between 1 (one) and NSK, let KT i be the <sort key>
contained in the i-th <sort specification> contained in OBCT.

6) For each i between 1 (one) and NSK,if KT i has the same left normal
form derivation as the <value expression> immediately contained in some
<derived column> DC of SLT, then:

NOTE 350 Left normal form derivation is defined in Subclause 6.1,
"Notation", in ISO/IEC 9075-1.

A) Case:

I) If DC simply contains an <as clause>, then let CN be the <column
name> contained in the <as clause>.

II) Otherwise, let CN be an implementation-dependent <column name>
that is not equivalent to the explicit or implicit <column name> of any
other <derived column> contained in SLT. Let VE be the <value expression>
simply contained in DC. DC is replaced in SLT by VE AS CN

B) KT i is replaced in OBCT by CN

7) Let SCR be the set of <column reference>s to columns of TE that
remain in OBCT after the preceding transformation.

8) Let NSCR be the number of <column reference>s contained in SCR. For
each j between 1 (one) and NCR, let C j be an enumeration of these <column
reference>s.

9) Case:

A) If NSCR is 0 (zero), then let SKL be the zero-length string.

B) Otherwise, Let SKL be the comma-separated list of <derived column>s:
, C 1 , C 2 , ..., C NCR The columns C j are said to be extended sort key
columns.

10) Let ST be the result of evaluating the <query specification>:
SELECT SLT SKL TE

11) Let EOBC be OBCT.

ii) Otherwise, let ST be T and let EOBC be OBC.

f) ST is said to be a sort table.
----------------------------------------------------------