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

On Mon, 18 Aug 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > 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
>
> Hmm ... that section is not exactly crystal-clear, is it? I had been
> thinking of the part about deliberate incompatibilities with SQL92,
> but rereading that, I see it only says they've eliminated the ability
> to reference output columns by *number*, not by name.
>
> Yet if they merely want to say "we allow expressions in the output
> column names", why wouldn't they say that? This section is about ten
> times longer than it would need to be to express that concept. I get
> the impression that they're trying to compromise between allowing output
> column names and input column names, but I sure don't follow exactly how
> the compromise is supposed to work. And there are a ton of apparently-
> unnecessary restrictions (no grouping, no subqueries in the sort keys)
> that make me wonder what's going on.

My reading is basically:

You can make column references to output columns.

If you make column references to things that aren't output columns, then
the query must be a "simple table query" (as per the definition in e).
If the expression is not equivalent to one of the output value
expressions, the restrictions listed (no grouping, etc...) apply and treat
it as if you added the appropriate columns to the output select
list. ** This bit is very unclear, but it seems reasonable given the
mention of removing extended sort key columns from the output later in
the general rules. **
If it is equivalent to one of the output value expressions then act
as if the output column name was used instead of the expression.

You cannot use subqueries or set function in the order by.

-- This seems really different from our previous standard reading of SQL92
though. It implies that you can't really do stuff on input columns
except in very limited cases and that'd be really bad.