[development] Request to review Guidelines for writing MySQL and PostgreSQL compliant SQL

[Damien Tournoud]

Hey Jean-Michel,

Thanks for your hard work. Unfortunately, some of the information you
added to this handbook are either completely wrong or very misleading.
Here are a few remarks:

- http://drupal.org/node/338676: PostgreSQL 8.4 is not supported by
any Drupal version to date. I am not aware of any test results that
can confirm that it works correctly. PostgreSQL < 8.3 is recommended
for Drupal 6 (because PostgreSQL 8.3 became *very* picky about column
types), and PostgreSQL 8.3 is recommended for Drupal 7. That might
change before Drupal 7 is released, if test results prove that we
support PostgreSQL 8.4 correctly.

- http://drupal.org/node/555580: CONCAT() is supported by Drupal on
every database engine, but only with two parameters

- http://drupal.org/node/555558: WTF? I'm not sure you understand how
indexes work.

- http://drupal.org/node/555562 and http://drupal.org/node/555648:
it's enough to state that Drupal doesn't support multiple table DELETE
or UPDATE. MySQL has a long standing bug
(http://bugs.mysql.com/bug.php?id=5037) that prevents you from doing
proper subqueries in DELETE (apparently the index state is getting
mixed up). In that case, you need DELETE FROM table WHERE id = (SELECT
id FROM (SELECT id FROM table WHERE ...) AS temptable). Not that
elegant, but it works.
  => Drupal 7 is not affected

- http://drupal.org/node/555568: Yes. Drupal 7 is not affected either.

- http://drupal.org/node/555518: Those are *not* nested ORDER BY, but
a special syntax of Subquery, only supported by MySQL. It's enough to
say that you should use a proper syntax.

- http://drupal.org/node/555540: we need to make clear that putting a
random value in a boolean clause and expecting it will work is a bad
idea

- http://drupal.org/node/555536: this looks like a programming error
in the Event module, and has very little to do with automatic casting.

- http://drupal.org/node/555530: here you say that the MySQL parser is
"smart". It is not. What this query will return is the value of the
u.name column from a random line matching the aggregation. This is a
non deterministic query, and should be avoided.

- http://drupal.org/node/555548: per coding standard, we only use
single quotes in SQL queries. Drupal 7 uses PDO placeholders, so it is
not affected.

Damien Tournoud

2009/8/21 Jean-Michel Pouré <jm at poure.com>:
> Dear friends,
>
> Just a quick note that I added some information in the manual:
> Developing Drupal using PostgreSQL:
> http://drupal.org/node/338676
>
> Especially, "Guidelines for writing MySQL and PostgreSQL compliant SQL":
> http://drupal.org/node/555514
>
> This page lists major differences between MySQL and PostgreSQL syntax.
>
> Some issues can be fixed in D7 (some may already as I did not follow
> development). I saw compatibility functions around. Some automatic casts
> may be added also in D7 PostgreSQL schema. If this was done, please
> inform me and I will update the pages or update them yourself.
>
> Some need fixing in PostgreSQL. All issues require attention from
> developers.
>
> Would it be possible to review http://drupal.org/node/555514
> to make sure anyone agrees on the standard SQL to use for both MySQL and
> PostgreSQL.
>
> When it is done, I will contact PostgreSQL hackers to ask for
> modifications in PostgreSQL core. Some modifications seem to be in the
> scope of possibility.
>
> Besides, as stated before, I think we would gain to flag minimal
> PostgreSQL version to 8.4 to benefit from PostgreSQL 8.4 new versions
> and from a better compatibility with Drupal.
>
> PostgreSQL new stable release is PostgreSQL 8.4, even if Debian flags it
> as unstable. I could upgrade some 500.000 pages sites to PostgreSQL 8.4
> without running into a single problem. SO IMHO let us not stick to an
> old PG version.
>
> This is a minor issue, please focus on http://drupal.org/node/555514
>
> Kind regards,
> Jean-Michel
>