From: Chris Travers <chris@travelamericas.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Anthony Molinaro <amolinaro@wgen.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Rick Schumeyer <rschumeyer@ieee.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: pg, mysql comparison with "group by" clause
Date: Thu, 20 Oct 2005 11:31:40 -0700
Message-ID: <4357E28C.3070401@travelamericas.com> (raw)
In-Reply-To: <87oe5uyrfs.fsf@stark.xeocode.com>
References: <3C6C2B281FD3E74C9F7C9D5B1EDA4582182614@wgexch01.wgenhq.net>
<87oe5uyrfs.fsf@stark.xeocode.com>
Greg Stark wrote:
>"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.
>
>
I sort of see what you are saying.... but you have yet to convince me....
>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.
>
>
Well.... The question is really whether two things should be true:
1) whether you want to assume that the programmer is going to know
about Single/Multi Value Dependency issues per column. IMO, this is
more of a DB design issue than a client app issue. And I would *not*
want to make that assumption because for higher normal forms where this
is likely to be a consideration, you are likely to have denormalized
access via VIEWs anyway.
2) Whether you are willing to rely on looking at the data first to
determine whether the query is valid
Alternatively we are back to the ability to get the wrong answer with
ease and in very difficult to debug ways. I suspect that MySQL places
an implicit MIN() around columns not included in the group by
statement. I fail to see why this is not an appropriate answer to his
concern.
>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
>
>
Ok. You have a few choices:
SELECT MIN(dept_id), dept_name, count(*) FROM employee_list GROUP BY
dept_name;
SELECT dept_id, dept_name, count(*) FROM employee_list GROUP BY
dept_name, dept_id;
SELECT dept_id, MIN(dept_name), count(*) FROM employee_list GROUP BY
dept_id;
And yes, it is bad design in every case I can think of.//
Why is this a problem?
Best Wishes,
Chris Travers
Metatron Technology Consulting
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: chris@travelamericas.com, gsstark@mit.edu, amolinaro@wgen.net, tgl@sss.pgh.pa.us, smarlowe@g2switchworks.com, sszabo@megazone.bigpanda.com, rschumeyer@ieee.org
Subject: Re: pg, mysql comparison with "group by" clause
In-Reply-To: <4357E28C.3070401@travelamericas.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