Re: Functional dependencies and GROUP BY

On Mon, Jun 7, 2010 at 6:41 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
>> This is frequently requested by MySQL converts (and possibly others).
>
> I'd certainly love to see it- but let's not confuse people by implying
> that it would actually act the way MySQL does.  It wouldn't, because
> what MySQL does is alot closer to 'distinct on' and is patently insane
> to boot.  Again, I'd *love* to see this be done in PG, but when we
> document it and tell people about it, *please* don't say it's similar in
> any way to MySQL's "oh, we'll just pick a random value from the columns
> that aren't group'd on" implementation.

Preface: I work as a MySQL DBA (yeah, yeah, laugh it up...).

It has been my experience that the vast majority of the time when a
MySQL users make use of the "fine feature" which allows them to use
unaggregated columns which is not present in the GROUP BY clause in an
aggregating query they have made an error because they do not
understand GROUP BY. I have found this lack of understanding to be
very wide spread across the MySQL developer and *DBA* communities. I
also would really hesitate to compare this useful feature to the *fine
feature* present in MySQL. Due to a long standing bug
(http://bugs.mysql.com/bug.php?id=8510) it really is not possible to
get MySQL to behave sanely. It is my impression that many programs of
significant size that interact with MySQL have errors because of this
issue and it would be good to not claim to have made Postgres
compatible.

That said, I imagine if this feature could make it into the Postgres
tree it would be very useful.

Would I be correct in assuming that while this feature would make
query planning more expensive, it would also often decrease the cost
of execution?

Best,

Rob Wultsch
wultsch(at)gmail(dot)com