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: | Morgan Tocker | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
Version: | OS: | Any | |
Assigned to: | |||
Triage: | Needs Triage: D5 (Feature request) |
[19 Jun 2014 15:55] Morgan Tocker
[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 <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 11:12] Umesh Umesh
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
[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.