SQL compatibility reminder: MySQL vs PostgreSQL
in [PgSql]
|
Prev: Visual Studio 2005, C-language function - avoiding hacks?
Next: [HACKERS] Core dump running PL/Perl installcheck with bleadperl [PATCH]
From: Dave Page on 5 Mar 2010 09:55 2010/3/5 François Pérou <francois.perou(a)free.fr>: > Dear friends, > > As a reminder, I took part in the development of pgAdmin and I am not > looking for a flame war. What did you work on François? I can't find your name in my email archives or on archives.postgresql.org. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Dunstan on 5 Mar 2010 10:14 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 || || -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Merlin Moncure on 5 Mar 2010 10:28 2010/3/5 François Pérou <francois.perou(a)free.fr>: > => All non-aggregate fields must be present in the GROUP BY clause > http://drupal.org/node/555530 My take is that this is never going to happen unless we are strictly talking about cases where the non-aggregate fields can be unambiguously determined. If we aren't, mysql is wrong to allow this, and developers that depend on it are wrong, and that is pretty much all you are ever going to get from this list. :-) The other stuff is mainly tangential fluff issues (takes 1% extra effort to write portable sql for) except for the flexible multi table delete, which would be nice although I wouldn't expect a strict copy of mysql syntax. I am personally looking at writeable CTE (which didn't make 9.0) to do most of the things I would need to do with a multi table delete feature, plus a quite a few other things. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 5 Mar 2010 11:39 2010/3/5 François Pérou <francois.perou(a)free.fr>: > Dear friends, > > As a reminder, I took part in the development of pgAdmin and I am not > looking for a flame war. > > I would like to point out Drupal community efforts (including myself) to > write down the most frequent problems when porting MySQL from/to > PostgreSQL: > > The main MySQL/PostgreSQL issues can be found here: > http://drupal.org/node/555514 > > 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 > > These ones are less urgent, but still needed to ease migration: > > => Use SELECT(DISTINCT ) only for one field, use SELECT COUNT(*) FROM > (SELECT DISTINCT ... ) ... for multiple > http://drupal.org/node/706264 > > => DELETE SYNTAX on several tables requires the USING syntax: > http://drupal.org/node/555562 Interestingly, all there of these are cases where a portable syntax is available, but at least some Drupal developers have chosen not to use it. All three web pages include a description of the portable syntax, and a suggestion that it be used. So the case that we should modify PostgreSQL to support MySQL-specific syntax seems pretty weak. Nor is it the case that every other database in the world handles these like MySQL and only PostgreSQL does the opposite. In fact it's closer to the other way around. For example, Microsoft SQL Server generates this error on your first query: Column 'u.uid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. PostgreSQL says: ERROR: column "u.uid" must appear in the GROUP BY clause or be used in an aggregate function And it sounds like Oracle may do something similar: http://searchoracle.techtarget.com/answer/Invalid-GROUP-BY-SQL-query Your complaint about SELECT COUNT(DISTINCT...) is similar. There is a perfectly portable way to write this that works in all database engines, but some Drupal developers have chosen to write it in a way that only works in some database engines. Why not use the portable method? In fact, the docs already recommend using the portable method; this seems like a non-issue. The docs also say that PostgreSQL will support the other syntax beginning in version 9.0, but I'm not certain that's correct. > IMHO, it is no use working on complex issues like replication if the SQL > code of major softwares cannot run on PostgreSQL. I think it would be great if Drupal ran on PostgreSQL, but I don't believe that the solution is for PostgreSQL to support whatever syntax Drupal happens to use. I think the solution is for Drupal to use syntax that works on more than one database, as is already suggested by the web pages listed above. Sounds like for about the same amount of work they could pick up Oracle and Microsoft SQL server support as well. > IMHO, 99% Drupal developers do not have a deep knowledge in SQL: > > * For example, part of Drupal developers are trying to find an automated > way to insert DISTINCT on queries automatically using PHP preg. Of > course, this creates bugs, which go on and on for years. The attempt can > be seen here: http://drupal.org/node/284392 (>400 replies). It could > well be 10 years more bugs in this thread. Interestingly the very first reply here includes this phrase: "Wow, and here I thought Drupal 6 would finally have fixed various db_rewrite_sql bugs." And reply #145 includes: "This is always what happens when using MySQL. Franckly, you should always use PostgreSQL and read detailed logs to understand how the parser works." > * Another very funny thing from Drupal community is that MySQL trims > data without complaining, which is not the case for PostgreSQL: > http://drupal.org/node/279219 That's a feature, and the MySQL behavior is a bug. From reply #7 on that thread: "When inserting TEXT into a VARCHAR(255), MySQL trims the value to the first 255 characters. PostgreSQL complains and returns an error, which the correct behavior. I hope that Drupal can get fixed on this issue ... As MySQL does not complain, this bug is unseen. It is maybe Drupal most annoying bug, as it trims and destroys data, and noone complains." > But there is no way to change people. It looks like PostgreSQL SQL > syntax and parser should evolve to become more tolerant. > > If PostgreSQL syntax was more tolerant, Drupal developers would be > interested in leaving MySQL for PostgreSQL. SO PLEASE take a deep look > at my request. > > So what are your plans for PostgreSQL 9? Do you finally plan to beat > MySQL? I finally abandoned MySQL completely seven years ago because the query planner was so poor that no matter what I did I couldn't get even moderately complex queries to perform decently. So for my use case PostgreSQL had MySQL beat even back then. The main reason I stuck with MySQL as long as I did is that the first versions of PostgreSQL that I used didn't support things like dropping columns (that feature was added in 2002) which was inconvenient. Needless to say that's ancient history at this point. But even back then it seemed to me that it was worth enduring some temporary inconvenience to move from a database that *would not work for my queries at all no matter what* to one that *would require some adjustments to my queries*. And PostgreSQL has made enormous progress on almost every front since then. I think it's pretty funny that major features like replication don't seem as important to you as making PostgreSQL support certain bits of MySQL-specific syntax. I think I speak for most people here when I say that you're probably best off sticking with MySQL in that case. I expect many new PostgreSQL features over the next several years (much as there have been over the past several years) that will allow me to do really cool things that I can't do right now, as well as continuing performance enhancements. These will be real, substantive features, not just syntax changes. I do expect that there will be some work toward syntax and feature compatibility with other databases, but I suspect for the most part we'll be looking at Oracle and the SQL standard rather than MySQL. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: =?ISO-8859-1?Q?Fran=E7ois_P=E9rou?= on 5 Mar 2010 12:26
Thanks for your answers. To speak frankly: * I wrote the Drupal guide for porting from MySQL to PostgreSQL. * I am also the author of remarks about people should use PostgreSQL to write portable SQL. * I am very surprised by the SQL level of Php developers. The example Drupal developers trying to rewrite SQL queries dynamically adding DISTINCT clause is just an example. So don't expect them to understand the difference between MySQL and PostgreSQL. It is out of reach. They focuse on Php code. * I got banned from Drupal website during 2 days because I opened a bug complaining about a long running SQL query that moved the whole content of a 20.000 rows forum into PHP variables just to display 'Previous' and 'Next' links. I had to write Dries Buytaert to get unbanned. Then Prev and Next features got removed from Drupal. They did not even try to use SELECT FROM ... LIMIT ... OFFSET to find prev and next records. * Php developers analyze database performance using PHP cache. They never read MySQL logging information. I guess they don't have such information, as on some providers, MySQL is configured without logging (for ... speed as MySQL configuration states). So they use Php code to display performance information. All this is true. Nevertheless, I feel my explanations are useless. This is like fighting against the wind. I believe that PostgreSQL should support more MySQLisms in order to BEAT MySQL. Feel free to use my guide on Drupal website. We have to adapt tools to people, not the converse. Kind regards, Jean-Michel Pouré -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |