On Mon, 18 Aug 2003, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > -- 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.
>
> Yes, it seems fraught with bogus restrictions, which makes me wonder if
> we're interpreting it correctly.
>
> I could understand a definition that says "unqualified names are first
> sought as output column names, and if no match then treated as input
> column names; qualified names are always input column names". Perhaps
> that's what they're really trying to do, but why all the strange
> verbiage?
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 since in none of those cases does
the ordering really make sense because you can't necessarily distinctly
choose a value for sorting for each output row (or the output row in the
last case).
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). I believe that
the grammar for query expression seems to allow something like FOO INNER
JOIN BAR ON (FOO.A=BAR.B) as an entire query expression without a SELECT
or select list -- and that would be disallowed as well --, but AFAIK we
don't support that anyway.
So the rules for the input column references are:
You cannot do it through distinct, group by, set functions or
UNION/INTERSECT/EXCEPT. You can also not do it through some wierd SQL99
constructs we don't support. :)