Date: Fri, 21 Feb 2003 23:57:48 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dan Langille <dan@langille.org>, <pgsql-bugs@postgresql.org>,
	<pgsql-sql@postgresql.org>
Subject: Re: [SQL] 7.3 GROUP BY differs from 7.2 
In-Reply-To: <2438.1045884536@sss.pgh.pa.us>
Message-ID: <20030221235057.H66979-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 21 Feb 2003, Tom Lane wrote:

> Dan Langille <dan@langille.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).