Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-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

pgsql-hackers by date

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

pgsql-advocacy by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group