Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> 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.
But this is bogus. What is wrong with
SELECT a, max(b) FROM t GROUP BY a ORDER BY min(c)
It would certainly be legal as
SELECT a, max(b), min(c) AS ocol FROM t GROUP BY a ORDER BY ocol
but SQL99 seems to be written so that you can't write the former ---
which leaves me wondering exactly what they mean by features E121-02
and E121-03 ...
After reading over the spec again I finally realized the significance of
this bit:
i) Let X be any <column reference> directly contained in K(i).
ii) If X does not contain an explicit <table or query name> or
<correlation name>, then K(i) shall be a <column name> that
shall be equivalent to the name of exactly one column of
ST.
Although they manage not to say so in so many words, it seems their
solution to the output-column-name vs input-column-name ambiguity is
that unqualified names in ORDER BY are output names, and qualified names
are input names. Period, no alternatives.
I think we'd create too much of a backwards compatibility problem for
ourselves if we adopt this verbatim. I could go for (a) qualified names
are input columns, (b) unqualified names are sought first as output
columns and second as input columns. This would accept every SQL99-
or SQL92-compatible query correctly. It would also accept most queries
that we've historically accepted -- the gotchas would come if you rename
an output column with a name that conflicts with an input column, and
then refer to that (unqualified) name in an ORDER BY expression. That
seems like a pretty small population of problems.
As for the other restrictions in the spec, I say lose 'em. If an
expression would be valid as a SELECT-list entry, it should be valid in
ORDER BY.
(I have no idea exactly how hard this would be to implement, btw. I
think the existing infrastructure for unnamed joins might help, but
I'm not sure.)
regards, tom lane
