To: "Anthony Molinaro" <amolinaro@wgen.net>
Cc: <gsstark@mit.edu>, "Tom Lane" <tgl@sss.pgh.pa.us>,
	"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
References: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182614@wgexch01.wgenhq.net>
In-Reply-To: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182614@wgexch01.wgenhq.net>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 13 Oct 2005 00:24:55 -0400
Message-ID: <87oe5uyrfs.fsf@stark.xeocode.com>
Lines: 37
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Anthony Molinaro" <amolinaro@wgen.net> writes:

> Greg,
>   You'll have to pardon me...
>  
> I saw this comment:
> 
> "I don't see why you think people stumble on this by accident. 
> I think it's actually an extremely common need."
> 
> Which, if referring to the ability to have items in the select that do not
> need to be included in the group, (excluding constants and the like) is just
> silly.

Well the "constants and the like" are precisely the point. There are plenty of
cases where adding the column to the GROUP BY is unnecessary and since
Postgres makes no attempt to prune them out, inefficient. And constants aren't
the only such case. The most common case is columns that are coming from a
table where the primary key is already included in the GROUP BY list.

In the case of columns coming from a table where the primary key is already in
the GROUP BY list it's possible for the database to deduce that it's
unnecessary to group on that column. 

But it's also possible to have cases where the programmer has out of band
knowledge that it's unnecessary but the database doesn't have that knowledge.
The most obvious case that comes to mind is a denormalized data model that
includes a redundant column.

  select dept_id, dept_name, count(*) from employee_list

For example if dept_name is guaranteed to be the same for all records with the
same dept_id. Of course that's generally considered poor design but it doesn't
mean there aren't thousands of databases out there with data models like that.

-- 
greg