MySQLism: MySQL will automatically cast INT into VARCHAR/CHAR

SQL-99 forbids to cast data between integer and alphanumerical values. The reason is that such a cast may vary depending on locales and the representation of numbers. For example, 1.2 (integer) can be '1,2' in French and '1.2' in English. This is only one valid reason for not casting, there may be several other reasons.

MySQL allows to cast data from any type to any type in SQL queries.

This is not the case of PostgreSQL, which only automatically casts data:

• From any numerical type to any numerical type
• From any alphanumerical type to any alphanumerical type
• From any date type to any date type

In the case of PostgreSQL, this prevents wrong casts when different locales or timestamps are used.

This query works under MySQL and fails under PostgreSQL:

UPDATE users SET timezone_id = 320 WHERE timezone = 7200 AND timezone_id = 0

The reason is that timezone is a CHAR value and not an integer.

A possible solution is to cast the data explicitely:

UPDATE users SET timezone_id = 320 WHERE timezone = CAST(7200 AS VARCHAR) AND timezone_id = 0

I could not yet test this cast under MySQL. Can you confirm this works?

This problem denotes that some types were not chosen correctly. Drupal schema needs some minor fixes. Maybe the database layer should allow abstraction to promote / convert types.

Reference: http://drupal.org/node/555132