Peter,
> This does not work because the first select list item references a column
> inside a join, which is not (necessarily) mathematically identical to the
> column that arrives outside of the join and is in the GROUP BY clause.
> (Think of an outer join: the column outside the join might contain added
> null values. Of course you are using an inner join, but the constructs
> work the same either way.)
Hmmm ... I don't see that. I can see that it might be practically
difficult-to-impossible to make the planner distinguish between cases where
the columns in the select list are different from the columns in the JOIN
series and when they aren't, but as a theory issue I just don't buy it.
What Dan's doing is:
SELECT a.c1, count(a.c2)
FROM a JOIN b
WHERE b.c5 = x
GROUP BY c1
In a case like this, a.c1 == c1 without possibility of ambiguity. The only
difference is whether or not a table qualifier is used on the name, which in
theory should *only* make a difference when there are more than one table in
the query with that column name.
So if you're saying that that kind of equivalency is a challenging parser
implementation issue, then I'm with you. If you're saying its a SQL theory
issue, though, I don't agree at all.
--
Josh Berkus
Aglio Database Solutions
San Francisco
