SQL compatibility reminder: MySQL vs PostgreSQL

2010/3/5 François Pérou <francois.perou@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

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&gt;:

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

||
||

2010/3/5 François Pérou <francois.perou@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

2010/3/5 François Pérou <francois.perou@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

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é

2010/3/5 François Pérou <francois.perou@free.fr>:

* 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 think you're still missing the point. I am sorry that the Drupal
developers (at least in your opinion) do not understand the difference
between MySQL and PostgreSQL, but I don't feel like it's our problem
to fix that. As far as I can tell, no promiment member of this
community agrees with any of the changes you are proposing.

I have a certain feeling of deja vu about this whole conversation:

http://archives.postgresql.org/pgsql-hackers/2009-08/msg01491.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01500.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01494.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01495.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01492.php

...Robert

François Pérou wrote:

I believe that PostgreSQL should support more MySQLisms in order to BEAT
MySQL.

Our aim is not to beat MySQL. At least mine is not, and I don't think
I'm alone. Many of the MySQLisms you want supported are just broken
behaviour, in the view of many people. So you are not going to win
arguments using this line of reasoning, IMNSHO.

cheers

andrew

2010/3/5 François Pérou <francois.perou@free.fr>:

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é

This is confusing advocacy and technical considerations.

Yes PHP coders are often deeply ignorant of database issues, many of
them deliberately and militantly so. I have had my altercations with
the Drupal community over dumb SQL and uncritical praise of document
databases as a pancea. MySQL has a set of characteristics (maybe even
deliberately) which work well for uptake in that market.

One of the reasons I like Postgres is the feeling I get that the
product and the community around it would like to make better
databases. They would like to use databases better and make it
possible for others to use databases better. On that front MySQL is
already beaten.

Just as the abuse of spreadsheets for data management will probably
never be properly vanquished, the permissiveness of MySQL will always
present a lower hurdle to some coders.

The perception of MySQL as enemy number one does also concern me a
bit. Postgres is competing for installed base on a far wider front
than that. If installed base is really that meaningful a competition
in the first place.

Bell.

2010/3/5 François Pérou <francois.perou@free.fr>:

I believe that PostgreSQL should support more MySQLisms in order to BEAT
MySQL.

we BEAT mysql long ago... to make postgres as broken as mysql is not
an improve...

Feel free to use my guide on Drupal website. We have to adapt tools to
people, not the converse.

with that reasoning, then Galileo and Copernico should had faken their
tests to adjust the results to most people expections about the earth
being the center of the universe and our sun orbiting us

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Fri, 2010-03-05 at 12:42 -0500, Robert Haas wrote:

2010/3/5 François Pérou <francois.perou@free.fr>:

* 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 think you're still missing the point. I am sorry that the Drupal
developers (at least in your opinion) do not understand the difference
between MySQL and PostgreSQL, but I don't feel like it's our problem
to fix that. As far as I can tell, no promiment member of this
community agrees with any of the changes you are proposing.

Correct.

Not to mention as far as I understand it, 99% of this is resolved in
Drupal 7.

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

On Fri, Mar 5, 2010 at 9:55 AM, Dave Page <dpage@pgadmin.org> wrote:

2010/3/5 François Pérou <francois.perou@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.

I believe the OP is the same person as Jean-Michel Poure
<jm@poure.com>. And I believe we discussed many of these same things
back in August. And now he is posting them over again just to see if
he gets an answer he likes better the second time. Perhaps Greg Stark
summed it up best:

http://archives.postgresql.org/pgsql-hackers/2009-08/msg01818.php

...Robert

All,

Given that Francois seems to return to this list every 3 months with the
exact same set of requests, I think we need to make a habit of ignoring
him the way we used to ignore Al Dev (although I'll comment that Al Dev
was *much* more entertaining).

Several members of our community are working with Drupal project leaders
to help them make Drupal more database-independant, and version 7 is
making great strides in this direction. If there's an next step, it's
*our* community providing a test instance of PostgreSQL which Drupal
developers can test their modules against, and a pgsql-drupal list or
similar for them to work out the problems quickly and easily.

Neither of the Drupal project leaders I've talked with wanted PostgreSQL
to support more whacky MySQL syntax. Francois is out on his own here.

And, Francois, it's not our goal to beat MySQL. Nobody can be a better
MySQL than MySQL, ever.

Our goal is to make the best possible SQL-Relational database.

--Josh Berkus

On Fri, Mar 5, 2010 at 1:48 PM, Josh Berkus <josh@agliodbs.com> wrote:

Given that Francois seems to return to this list every 3 months with the
exact same set of requests, I think we need to make a habit of ignoring
him the way we used to ignore Al Dev (although I'll comment that Al Dev
was *much* more entertaining).

Perhaps we should refer Francois/Jean-Michele to Al Dev's comments on
MySQL. :-)

http://www.yolinux.com/HOWTO/PostgreSQL-HOWTO.html#ss4.2

...Robert

On Fri, Mar 05, 2010 at 02:56:23PM +0100, Fran�ois P�rou wrote:

Dear friends,

As a reminder, I took part in the development of pgAdmin and I am
not looking for a flame war.

You're doing a poor job on that latter. You asked before for the
PostgreSQL project to "address" the concerns of some, but by no means
all, developers of some little project by introducing massive bugs.

That is never going to happen, and you need to stop asking.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:

On Fri, Mar 05, 2010 at 02:56:23PM +0100, Fran�ois P�rou wrote:

Dear friends,

As a reminder, I took part in the development of pgAdmin and I am
not looking for a flame war.

You're doing a poor job on that latter. You asked before for the
PostgreSQL project to "address" the concerns of some, but by no means
all, developers of some little project by introducing massive bugs.

Drupal is not a little project. Let's keep our own facts straight.

cheers

andrew

On Fri, 2010-03-05 at 15:10 -0500, Andrew Dunstan wrote:

David Fetter wrote:

On Fri, Mar 05, 2010 at 02:56:23PM +0100, François Pérou wrote:

Dear friends,

As a reminder, I took part in the development of pgAdmin and I am
not looking for a flame war.

You're doing a poor job on that latter. You asked before for the
PostgreSQL project to "address" the concerns of some, but by no means
all, developers of some little project by introducing massive bugs.

Drupal is not a little project. Let's keep our own facts straight.

Not it isn't, it dwarfs us I am sure. It is within our interest to work
with them but in a proper way. As I have said, Drupal 7 addresses most
of our concerns, so anything we want to complain about should be in the
interest of making sure Drupal 7 works properly, not previous versions.

Sincerely,

Joshua D. Drake

cheers

andrew

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

On Fri, Mar 05, 2010 at 03:10:59PM -0500, Andrew Dunstan wrote:

David Fetter wrote:

On Fri, Mar 05, 2010 at 02:56:23PM +0100, Fran�ois P�rou wrote:

Dear friends,

As a reminder, I took part in the development of pgAdmin and I am
not looking for a flame war.

You're doing a poor job on that latter. You asked before for the
PostgreSQL project to "address" the concerns of some, but by no means
all, developers of some little project by introducing massive bugs.

Drupal is not a little project. Let's keep our own facts straight.

Drupal 6 support is, given its finite future. He's basically asking
us to do something that Drupal 7 and later won't need or want, so I
stick by "little."

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Fri, Mar 5, 2010 at 6:25 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Fri, Mar 5, 2010 at 9:55 AM, Dave Page <dpage@pgadmin.org> wrote:

2010/3/5 François Pérou <francois.perou@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.

I believe the OP is the same person as Jean-Michel Poure

Yeah, I had email from him offlist. FYI, whilst I disagree that we
should break Postgres for the sake of some lazy developers on another
project, Jean-Michel was a long-term major contributor to pgAdmin I
and II and I have a lot of respect for him and am extremely grateful
for his past work on the project.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do

On Fri, Mar 5, 2010 at 3:30 PM, Dave Page <dpage@pgadmin.org> wrote:

On Fri, Mar 5, 2010 at 6:25 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Fri, Mar 5, 2010 at 9:55 AM, Dave Page <dpage@pgadmin.org> wrote:

2010/3/5 François Pérou <francois.perou@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.

I believe the OP is the same person as Jean-Michel Poure

Yeah, I had email from him offlist. FYI, whilst I disagree that we
should break Postgres for the sake of some lazy developers on another
project, Jean-Michel was a long-term major contributor to pgAdmin I
and II and I have a lot of respect for him and am extremely grateful
for his past work on the project.

That is good to hear. I do not think (nor do I think anyone else here
thinks) that making Drupal work with PostgreSQL is a bad idea, and I
hope that he and others will continue to pursue that goal - 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.

...Robert