你的位置:首页 > 数据库

[数据库]CAST function should support INT synonym for SIGNED. i.e. CAST(y AS INT)


 

 

Login / Register




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

 

Bug #73054CAST function should support INT synonym for SIGNED. i.e. CAST(y AS INT)
Submitted:19 Jun 2014 15:55Modified:30 Jun 2014 11:12
Reporter:Morgan TockerEmail Updates:
Status:VerifiedImpact on me:
None 
Category:MySQL Server: DMLSeverity: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/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 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 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.





泰国旅游几月份最好泰国旅游怎么样几月去泰国旅游最便宜怎样去泰国旅游7月泰国旅游从化天适樱花悠乐园樱花最佳观赏时间?天适樱花悠乐园樱花几月份是盛花期? 2015漳浦闽台美食节有哪些特色美食? 天适从化樱花悠乐园游玩需要多久?天适樱花悠乐园一日游路线? 《奔跑吧兄弟》电影什么时候上映?有哪些演员? 金谷羊角山好玩吗?佛冈金谷羊角山漂流一天游攻略? 阳朔菩萨水岩网上购票?菩萨水岩怎么预约? 阳朔菩萨水岩网上订票?菩萨水岩预约价格? 佛冈羊角山团购优惠吗?清远羊角山漂流团购门票在哪买? 宝晶宫暑假有什么活动?英德宝晶宫暑期优惠? 宝晶宫高考生优惠活动?英德宝晶宫凭准考证半价吗? 暑假去深圳野生动物园有什么好玩的?深圳野生动物园暑期活动? 深圳海洋天地表演时间?深圳动物园海洋天地有什么表演? 玩转2015世界杯举办地之巴西利亚 中国旅游老三难:涨价太猛 景区太挤 休假扎堆 女性出境最佳旅游目的地盘点_全球最适合女性出游的五大目的地 越南越美,走近诗意西贡,记住的不只是贫穷 MG02-1206Q7NC Datasheet MG02-1206Q7NC Datasheet MG02-1206Q7RC Datasheet MG02-1206Q7RC Datasheet MG02-1206Q7YC Datasheet MG02-1206Q7YC Datasheet 嵩山少林寺景区 嵩山少林寺景区 嵩山少林寺景区 世纪公园门票价格 世纪公园门票价格 世纪公园门票价格 洛阳桥在哪里 洛阳桥在哪里 洛阳桥在哪里