Re: [SQL] 7.3 GROUP BY differs from 7.2

On Sat, 22 Feb 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > On Fri, 21 Feb 2003, Tom Lane wrote:
> >> Anyone care to offer a gloss on the spec to prove that this behavior
> >> is correct or not correct?
>
> > Hmm, I'd read SQL92 Section 7.5 (joined tables) Syntax Rules as saying
> > that the non natural/using case is separate from the other cases.
>
> > Syntax rule 5 seems to say that A JOIN B ON (...) should have the same
> > column descriptors as A,B and it explicitly doesn't cover NATURAL or
> > USING (covered by rule 6).
>
> Yeah, but those rules only define the names and types of the JOIN's
> output columns. They don't say anything about the visibility of those
> names, nor the visibility of the underlying-table column names, nor
> particularly about semantic equivalence of the two sets of names.
>
> I spent some time digging around in the verbiage about name scopes,
> but didn't find any joy. It does seem clear that if you stick an
> alias on the JOIN as a whole, that that hides the individual table
> names/aliases, which would render the issue moot. But Dan didn't
> do that in his example, so he is allowed to access both the join
> columns and the underlying columns. Question is, are they equivalent
> for the purposes of the grouped-column-reference rules, or not?

Well, I mentioned it because I thought that it might mean that the
semantic equivalence for columns would be the same as the A,B case
(barring aliases, using, etc) since the only rule about the visibility on
the names in that case I could find was that you can't qualify to a table
within a joined table on a "common column name" of the joined table.
I'd think the rule would have to be the same for the A,B case as well.

And, if say A.A and <output of join> A are ambiguous on whether they're
the same, wouldn't the rules on group by say that A.A is invalid grouping
column reference in this case whether or not the select list has A or A.A?
I'm thinking that it wouldn't be an unambiguous reference to a column of
the preceding FROM/WHERE clause.