Re: SQL compatibility reminder: MySQL vs PostgreSQL

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, francois(dot)perou(at)free(dot)fr, pgsql-hackers(at)postgresql(dot)org
Subject: Re: SQL compatibility reminder: MySQL vs PostgreSQL
Date: 2010-03-06 19:01:03
Message-ID: 4B92A66F.8030104@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-advocacy pgsql-hackers

On 3/6/10 1:05 AM, Craig Ringer wrote:
> Andrew Dunstan wrote:
>
>> But AIUI that won't be the same as the MySQL behaviour, as documented at
>> <http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html>:
>>
>> When using this feature, all rows in each group should have the same
>> values for the columns that are ommitted from the |GROUP BY| part.
>> The server is free to return any value from the group, so the
>> results are indeterminate unless all values are the same.
>
> That sounds a lot like the behavior of `DISTINCT ON (...)' and it'd
> actually be really rather useful to have under some circumstances.
>
> Whether it should be written as 'GROUP BY', though, isn't so clear.

I believe that functional dependencies for GROUP BY (that is, if you
group on the PK, it shouldn't be necessary to group on the other
columns) are in our TODO list already.

However, "The server is free to return any value from the group" doesn't
sound like the way we do things, ever. MySQL users might be OK with
queries which return an indeterminate answer; our users are not. You'll
notice that SELECT DISTINCT ON requires you to have an ORDER BY; that's
by design.

--Josh Berkus

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message François Pérou 2010-03-06 20:01:10 Re: SQL compatibility reminder: MySQL vs PostgreSQL
Previous Message Peter Eisentraut 2010-03-06 17:56:27 Re: Explicit psqlrc

Browse pgsql-advocacy by date

  From Date Subject
Next Message François Pérou 2010-03-06 20:01:10 Re: SQL compatibility reminder: MySQL vs PostgreSQL
Previous Message Craig Ringer 2010-03-06 09:05:17 Re: SQL compatibility reminder: MySQL vs PostgreSQL