Re: SQL compatibility reminder: MySQL vs PostgreSQL

François Pérou wrote:
>
> An important pending issue, which goes on and on for years:
>
> => All non-aggregate fields must be present in the GROUP BY clause
> http://drupal.org/node/555530
>
>
>

The trouble is that the bottom of this page looks like nonsense to me.

The reason that

|SELECT COUNT(nid) FROM node
WHERE nid > 0 AND type IN ('page')
ORDER BY nid
|

fails really has nothing to do with GROUP BY. It has to do with a
meaningless and silly ORDER BY clause:

andrew=# SELECT COUNT(nid) FROM node
andrew-# WHERE nid > 0 AND type IN ('page')
andrew-# ORDER BY nid;
ERROR: column "node.nid" must appear in the GROUP BY clause or be
used in an aggregate function

And it could be cured by using an alias:

SELECT COUNT(nid) as nid FROM node
WHERE nid > 0 AND type IN ('page')
ORDER BY nid;

or by omitting the ORDER BY altogether, or by using "ORDER BY 1".

I think this query is NOT, as the page states, equivalant to:

|SELECT COUNT(nid) FROM node
WHERE nid > 0 AND type IN ('page')
GROUP BY nid
ORDER BY nid
|

If it is equivalent in MySQL then MySQL is broken, IMNSHO, and there
would be no reason for us to mimic that brokenness. The first query
(with the order by removed) should produce a single row. The second
should produce one row per nid.

Now, there is an issue with GROUP BY that has the following TODO item,
which has not been done (and thus will not be in 9.0):

Add support for functional dependencies
This would allow omitting GROUP BY columns when grouping by the
primary key.

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.

It will only be usable when PostgreSQL can know that the omitted columns
have a single value for the group, i.e. you won't ever get a different
result by omitting a redundant GROUP BY column.

In general, our aim is not to mimic MySQL. Asking us to do so simply for
the sake of compatibility is just about a sure way to get people's backs
up around here. Try going to the MySQL folks and asking them to be more
compatible with Postgres, and see how far you get. It is quite possible
to write code that runs on multiple databases. Bugzilla (to mention one
I have had a personal hand in enabling) has been doing it for years.

cheers

andrew

||
||