Description:

Reported via twitter:

https://twitter.com/lukaseder/status/479288364106280960

Other databases will support CAST(y AS INT), but MySQL requires CAST(y AS SIGNED).  I tried reading the SQL-92 standard (not the easiest text to follow), and it doesn't seem to indicate it must be SIGNED.

Is it possible to support INT as an alias for interoperability?

How to repeat:

mysql [localhost] {msandbox} (test) > select version();

+-----------+

| version() |

+-----------+

| 5.7.4-m14 |

+-----------+

1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT CAST('5' as INT);

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 'INT)' at line 1

mysql [localhost] {msandbox} (test) > SELECT CAST('5' as SIGNED);

+---------------------+

| CAST('5' as SIGNED) |

+---------------------+

|                   5 |

+---------------------+

1 row in set (0.00 sec)

Suggested fix:

mysql [localhost] {msandbox} (test) > SELECT CAST('5' as INT);

+---------------------+

| CAST('5' as INT) |

+---------------------+

|                   5 |

+---------------------+

1 row in set (0.01 sec)

Thanks for reporting this, Morgan.

Some additional notes on my motivation (in the original Tweet). All other SQL dialects support the same type notation for CAST expressions as for column specifications in DDL. Concretely (citing parts from SQL-92):

General definition of <data type>

---------------------------------

         <data type> ::=

                <character string type> [ CHARACTER SET <character set specification> ]

              | <national character string type>

              | <bit string type>

              | <numeric type>

              | <datetime type>

              | <interval type>

         <character string type> ::=

                CHARACTER [ <left paren> <length> <right paren> ]

              | CHAR [ <left paren> <length> <right paren> ]

              | CHARACTER VARYING <left paren> <length> <right paren>

<data type> can be used in <cast specification>...

--------------------------------------------------

         <cast specification> ::=

              CAST <left paren> <cast operand> AS <cast target> <right paren>

         <cast target> ::=

                <domain name>

              | <data type>

... and also in <table definitions>

-----------------------------------

         <table definition> ::=

              CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE <table name>

                <table element list>

         <table element list> ::=

                <left paren> <table element> [ { <comma> <table element> }... ] <right paren>

         <table element> ::=

                <column definition>

              | <table constraint definition>

         <column definition> ::=

              <column name> { <data type> | <domain name> }

MySQL has its own way of dealing with data types when used in a <cast specification>. Apart from possibly historic reasons, it is unclear why there are two sets of data types that cannot be used interchangeably.

Obviously, this is not limited to INT types, but to all supported data types, which should be supported as <cast targets>.

Hello Morgan,

Thank you for the feature request!

// Oracle Database 11g

SQL> SELECT CAST ('5' AS INT) from dual;

CAST('5'ASINT)

--------------

             5

SQL> SELECT * FROM V$VERSION;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for 32-bit Windows: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

// MySQL 5.7

mysql> SELECT CAST('5' as INT);

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 'INT)' at line 1

mysql>

mysql> SELECT CAST('5' as SIGNED);

+---------------------+

| CAST('5' as SIGNED) |

+---------------------+

|                   5 |

+---------------------+

1 row in set (0.00 sec)

mysql>

Thanks,

Umesh

I think we miss some other things here.

MySQL has several sizes of int apart from them being signed or unsigned.

So the CAST() function should really be able to convert to these specific types as the resultant value may later be used in a comparison.

So probably we should have CAST( XXXX AS [unsigned] (big|medium|tiny|)int )  to be clearer here and to catch out of value cast conversions which probably at least in strict mode should generate an error.

I also miss the possibility of CASTing into a timestamp(X) value as these days we may find that useful.

The reason for the CAST() as far as I can see is to force the resultant type to be something very specific so that anywhere the value is later used there's no doubt what type and size it has.