CAST function should support INT synonym for …

2018-06-17 23:04:05来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

 

 

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:
None 
Category: MySQL Server: DML Severity: S4 (Feature request)
Version:   OS: Any
Assigned to:      
Triage: Needs Triage: D5 (Feature request)
  • View
  • Add Comment
  • Files
  • Developer
  • Edit Submission
  • View Progress Log
  • Contributions

[19 Jun 2014 15:55] Morgan Tocker
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)
[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.

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:mysql 批处理文件出错后继续执行

下一篇:使用scws组件分词和phpanalysis分词类实现简单的php分词搜索