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

On Mon, 18 Aug 2003, Tom Lane wrote:

> Stephan Szabo  writes:
> > Okay, I think many of the random restrictions (in 2a, the grouping,
> > distinct, set function spec) are to stop you from doing things like:
>
> >  select distinct a from table order by b;
> >  select a,min(b) from table group by a order by c;
> >  select count(*) from table order by a;
>
> > All of which seem badly defined to me
>
> Agreed, but restrictions on those grounds should be identical to the
> restrictions on what you can write in a SELECT-list item.  AFAICT the
> restrictions actually cited here are quite different.

I see that it's different for grouping for example because it doesn't
mention the grouping columns as being okay, although simple column
references to input names of grouping columns that are directly mentioned
in the select list are okay because of the equivalence.

I can't really think of any other way to interpret that section
particularly differently.  If it's a simple table query and the expression
is not equivalent to a select list item then it can't use distinct or
group by or a set function.  We might argue about the meaning of simple
table query or equivalent but 2.A.I and II seem pretty straightforward.
I'm a little vague on why they worded IV the way they did, but VI seems to
imply that you're adding items to the sort table (that you take out later)
in order to make the column references match up.

> > The whole definition of simple table query seems to boil down to the fact
> > that the query expression must be a query specification (which would
> > appear to kill UNION/INTERSECT/EXCEPT, which makes sense since input
> > column names aren't necessarily meaningful in that case).
>
> Right, you could only use output column names for an ORDER BY on a
> UNION/etc.  We have that restriction already.  But is that really all
> they're saying here?

The two cases I mentioned, union and its ilk and the loose joins without a
select list are the cases I could see going through the section on query
expression definition.  There could be more, but AFAICS
SELECT  FROM  seems to meet the
requirements mentioned by simple table query.