Re: [SQL] 7.3 GROUP BY differs from 7.2

On Fri, 21 Feb 2003, Tom Lane wrote:

> Dan Langille <dan(at)langille(dot)org> writes:
> > This is the query in question:
>
> > SELECT element_id as wle_element_id, COUNT(watch_list_id)
> > FROM watch_list JOIN watch_list_element
> > ON watch_list.id = watch_list_element.watch_list_id
> > WHERE watch_list.user_id = 1
> > GROUP BY watch_list_element.element_id;
>
> > ERROR: Attribute unnamed_join.element_id must be GROUPed or used in an
> > aggregate function
>
> The parser currently considers an output column of a JOIN to be a
> different variable from the corresponding column of the input table.
> Thus the above error message. While the distinction is without content
> in this example, it is extremely real in some nearby cases --- in
> particular, in NATURAL or USING full outer joins it's possible for one
> to be null when the other isn't. (And no, I don't think 7.2 got this
> right.)
>
> I'm having a hard time finding anything in the SQL spec that addresses
> this point specifically --- but I also cannot find anything that
> suggests that the name scope rules differ between outer and inner joins.
> So it would be difficult for them to assert that element_id and
> watch_list_element.element_id must be treated as equivalent here,
> when they are clearly not equivalent in related cases.
>
> 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).