Re: 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.
>
> 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