/
| Bug #73054 | CAST function should support INT synonym for SIGNED. i.e. CAST(y AS INT) | ||
|---|---|---|---|
| Submitted: | 19 Jun 2014 15:55 | Modified: | 30 Jun 2014 11:12 |
| Reporter: | Email Updates: | ||
| Status: | Verified | Impact on me: | None |
| Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
| Version: | OS: | Any | |
| Assigned to: | |||
| Triage: | Needs Triage: D5 (Feature request) | ||
- View
[19 Jun 2014 16:14] Lukas Eder
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 --------------------------------- ::=[ CHARACTER SET ] | | | | | ::= CHARACTER [ ] | CHAR [ ] | CHARACTER VARYING can be used in ...-------------------------------------------------- ::= CAST AS ::= | ... and also in -----------------------------------
::= CREATE [ { GLOBAL | LOCAL } TEMPORARY ] TABLE
::=
[ {
}... ]
| ::=
::= { | }MySQL has its own way of dealing with data types when used in a . 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 .
[30 Jun 2014 11:12] Umesh Umesh
Hello Morgan,Thank you for the feature request!// Oracle Database 11gSQL> SELECT CAST ('5' AS INT) from dual;CAST('5'ASINT)-------------- 5SQL> SELECT * FROM V$VERSION;BANNER--------------------------------------------------------------------------------Oracle Database 11g Express Edition Release 11.2.0.2.0 - ProductionPL/SQL Release 11.2.0.2.0 - ProductionCORE 11.2.0.2.0 ProductionTNS for 32-bit Windows: Version 11.2.0.2.0 - ProductionNLSRTL Version 11.2.0.2.0 - ProductionSQL>// MySQL 5.7mysql> 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 1mysql>mysql> SELECT CAST('5' as SIGNED);+---------------------+| CAST('5' as SIGNED) |+---------------------+| 5 |+---------------------+1 row in set (0.00 sec)mysql>Thanks,Umesh [7 Oct 2015 20:08] Simon Mudd
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.
Description:Reported via twitter: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 1mysql [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)