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
primarykey. 
 


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
allvalues 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



||
||