pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dan Langille <dan@langille.org>
Cc: pgsql-bugs@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] 7.3 GROUP BY differs from 7.2
Date: Fri, 21 Feb 2003 23:57:48 -0800 (PST)
Message-ID: <20030221235057.H66979-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <2438.1045884536@sss.pgh.pa.us>
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).
view thread (6+ messages) latest in thread
Message-ID: <20030221235057.H66979-100000@megazone23.bigpanda.com> Permalink: ../20030221235057.H66979-100000@megazone23.bigpanda.com/ Also on: postgresql.org/message-id/20030221235057.H66979-100000@megazone23.bigpanda.com ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-sql@postgresql.org Cc: sszabo@megazone23.bigpanda.com, tgl@sss.pgh.pa.us, dan@langille.org, pgsql-bugs@postgresql.org Subject: Re: [SQL] 7.3 GROUP BY differs from 7.2 In-Reply-To: <20030221235057.H66979-100000@megazone23.bigpanda.com> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox