To: Greg Stark <gsstark@mit.edu>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>,
	Stephan Szabo <sszabo@megazone.bigpanda.com>,
	Rick Schumeyer <rschumeyer@ieee.org>, pgsql-sql@postgresql.org
Subject: Re: pg, mysql comparison with "group by" clause 
In-reply-to: <87zmpez0au.fsf@stark.xeocode.com> 
References: <001001c5cea8$77cabc90$0300a8c0@dell8200>
	<20051011142658.C54076@megazone.bigpanda.com>
	<87br1u1jvq.fsf@stark.xeocode.com>
	<1129155875.29961.168.camel@state.g2switchworks.com>
	<87zmpez0au.fsf@stark.xeocode.com>
Comments: In-reply-to Greg Stark <gsstark@mit.edu>
	message dated "12 Oct 2005 21:13:29 -0400"
Date: Wed, 12 Oct 2005 21:50:46 -0400
Message-ID: <16344.1129168246@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Greg Stark <gsstark@mit.edu> writes:
> I think most MySQL users don't stumble on it, they learn it as the way
> to handle the common use case when you join a master table against a
> detail table and then want to aggregate all the detail records. In
> standard SQL you have to write GROUP BY ... and list every single
> column you need from the master table. Forcing the database to do a
> lot of redundant comparisons and sort on uselessly long keys where in
> fact you only really need it to sort and group by the primary key.

Actually, if you're grouping by a table's primary key, the SQL99 spec
says you shouldn't have to explicitly list the other columns from that
table --- they are guaranteed to have unique values per group anyway.
This is a single case in the "functional dependency" stuff.  That
verbiage is incredibly dense and I don't think we want to tackle all of
it any time soon, but the primary-key case probably wouldn't be very
hard to implement.  We really ought to have this in TODO ... I'm sure
it's been discussed before.

			regards, tom lane