CAST function shocould support INT synonym for SIGNED. I. e. CAST (y AS INT), synonymi. e

CAST function shocould support INT synonym for SIGNED. I. e. CAST (y AS INT), synonymi. e

 

Login/Register

• Developer Zone
• Bugs Home
• Report a bug
• Statistics
• Advanced search
• Saved searches
• Tags

 

Bug #73054.	CAST function shocould support INT synonym for SIGNED. I. e. CAST (y AS INT)
Submitted:	19 Jun 2014	Modified:	30 Jun 2014
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned:
Triage:	Needs Triage: D5 (Feature request)

• View
• Add Comment
• Files
• Developer
• Edit Submission
• View Progress Log
• Contributions

[19 Jun 2014] Morgan Tocker

Description:Reported via twitter:https://twitter.com/lukaseder/status/479288364106280960Other 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)

[19 Jun 2014] 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 <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>.

[30 Jun 2014] 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] 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.