Bug #62926 Support CAST(x AS INTEGER) for consistency, compatibility
Submitted: 25 Oct 2011 15:34 Modified: 25 Oct 2011 18:19
Reporter: Mike Pomraning Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: ansi, SQL

[25 Oct 2011 15:34] Mike Pomraning
Description:
Please consider changing current behavior for user consistency and better cross-RDBMS compatibility.

Issue:  "... CAST(x AS INTEGER) ..." is a SQL syntax error (ERROR 1064, 42000).  This behavior is documented (see, for instance, response to bug #61032).

**However,** this behavior is surprising and contrary to reasonable expectations for two reasons:

(1) Internal consistency.  It is inconsistent to allow an undecorated "INTEGER" data type in a CREATE TABLE statement but not to allow the same as the target of a CAST or CONVERT.

(2) Compatibility.  "INTEGER" is the SQL-standard data type, and other RDBMS support this (at least Oracle, Postgres, Firebird, SQLite3, Hsql).

How to repeat:
mysql> SELECT CAST(NULL AS SIGNED INTEGER);
+------------------------------+
| CAST(NULL AS SIGNED INTEGER) |
+------------------------------+
|                         NULL |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(NULL AS INTEGER);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER)' at line 1

Suggested fix:
Ideally:  interpret a plain "INTEGER" specifier in CASTs and CONVERTs as if the user had specified "SIGNED INTEGER".

Less ideal:  introduce a new sql_mode (implied by mode 'ansi') that forces this behavior.
[25 Oct 2011 16:41] Valeriy Kravchuk
Thank you for the feature request.
[25 Oct 2011 18:19] Mike Pomraning
I forgot to say, this change would be entirely backward compatible with existing SQL scripts.
[17 Jul 2014 18:45] Ryan Marsh
+1