Re: SQL compatibility reminder: MySQL vs PostgreSQL - Mailing list pgsql-hackers
From | Andrew Dunstan |
---|---|
Subject | Re: SQL compatibility reminder: MySQL vs PostgreSQL |
Date | |
Msg-id | 4B911FF0.3060004@dunslane.net Whole thread Raw |
In response to | SQL compatibility reminder: MySQL vs PostgreSQL (François Pérou <francois.perou@free.fr>) |
Responses |
Re: SQL compatibility reminder: MySQL vs PostgreSQL
|
List | pgsql-hackers |
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 || ||
pgsql-hackers by date: