Re: SQL compatibility reminder: MySQL vs PostgreSQL

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