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: =?ISO-8859-1?Q?Fran=E7ois_P=E9rou?= on 5 Mar 2010 16:31 Le vendredi 05 mars 2010 à 15:40 -0500, Robert Haas a écrit : > having > said that, asking us to make changes that are not based on solid > technical arguments, don't conform to the SQL standard, and most > important that we already clearly said we were not going to make is > not the way to get there. Okay, thank you all for these explanations. I was talking about flexibility in the SQL syntax, which is linked to the need to be flexible in front of developers communities which are not interested in SQL and therefore use broken tools like MySQL. Drupal 6 is just an example. D7 may be better, okay. A lot of applications are built with a bad SQL syntax. You cannot change the world. I was talking about flexibility to allow people to migrate and test other databases than MySQL and you stick to the idea of a "pure" SQL syntax, like ayatollahs preaching in the desert. If an SQL syntax is not pure, why not rewrite it and issue a warning. This would allow to run MySQL code nearly unchanged. All you say is NO, NOT PURE code. I will not ever post again on PostgreSQL mailing list about these issues. Everyone expressed their ideas. I admit my ideas are not supported by anyone. This is bad enough because I debugged hundreds of Drupal modules and wrote a short technical guide on D website. Too bad you are not listening for more flexibility. Bye. Jean-Michel -- 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: Craig Ringer on 6 Mar 2010 04:05 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. -- Craig Ringer -- 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: Josh Berkus on 6 Mar 2010 14:01 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 -- 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 6 Mar 2010 15:01 Le samedi 06 mars 2010 à 11:01 -0800, Josh Berkus a écrit : > 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 Dear Josh, I unregistered the list, to avoid a flame war. My opinion is that PostgreSQL should accept any MySQL syntax and return warnings. I believe that we should access even innodb syntax and turn it immediately into PostgreSQL tables. This would allow people with no interest in SQL to migrate from MySQL to PostgreSQL without any harm. During my work on Drupal, I noticed that PostgreSQL was very near to running any MySQL code. Most users with no interest in SQL try PostgreSQL during a few minutes and then stop at the first SQL error. If there was no errors but only warnings they would have a choice. Presently, they don't. PHP developers don't have time to invest in learning deep SQL. This leads to situations where 99% of the Internet base uses MySQL, not PostgreSQL. The situation is the same with Windows sucking 90% of OSes in the world with bad software design. I had a discussion with OVH, the first French provider with 500.000 clients. We discussed about the possibility for shared hosting in PostgreSQL. He asked me whether I would buy PostgreSQL shared hosting. I answered no, because I have dedicated servers. Then he told me I was the only person interested in PostgreSQL. You may discuss and discuss and say "Yeah, we are the best at PostgreSQL". Being the best does not suffice without sufficient user base. Okay, you can always pretend to fight at the level of DB2 or Oracle. At this point, I will not discuss further. The user base of MySQL is huge and it would be so nice that many people would migrate to PostgreSQL. I will continue using PostgreSQL and MySQL user base will continue to grow and one day it will be 1 PostgreSQL user for 1.000 MySQL users. This is life. People have a deep psychological addiction to their believes and ideas. IMHO, PostgreSQL has to be more flexible (in psychological terms) to understand MySQL user needs and answer them, just to give them a choice to migrate to PostgreSQL. All your discussions are about technical things and you think I make fun of Drupal developers. I only tried to point out psychological believes, which we have to understand to answer their needs and grow PostgreSQL user base. Kind regards, Jean-Michel -- 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: Mark Kirkwood on 6 Mar 2010 15:48
François Pérou wrote: > > I will continue using PostgreSQL and MySQL user base will continue to > grow and one day it will be 1 PostgreSQL user for 1.000 MySQL users. > > This is life. People have a deep psychological addiction to their > believes and ideas. IMHO, PostgreSQL has to be more flexible (in > psychological terms) to understand MySQL user needs and answer them, > just to give them a choice to migrate to PostgreSQL. > > All your discussions are about technical things and you think I make fun > of Drupal developers. I only tried to point out psychological believes, > which we have to understand to answer their needs and grow PostgreSQL > user base. > > > > I think the Drupal developers are addressing the main thrust of your concerns - one of the gentlemen I work with here at Catalyst (Josh Waihi) has spent considerable time working on Postgresql issues for Drupal 7. Last time I checked, Drupal 7 + Postgresql passes most of the regression tests. Maybe you could consider helping out making Drupal 7 + Postgresql pass the remaining ones? regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |