On Fri, 21 Feb 2003, Tom Lane wrote: > Dan Langille <[EMAIL PROTECTED]> 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). ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
