Re: 7.3 GROUP BY differs from 7.2

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?

regards, tom lane