Bug #30524 CAST(0 AS Double) needed
Submitted: 20 Aug 2007 22:24 Modified: 19 May 2019 23:42
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: cast, double, literal, numeric

[20 Aug 2007 22:24] Mark Callaghan 
Description:
Numeric literals have type Double in MySQL 4 and type Decimal in MySQL 5. In some cases, the semantics of Double are preferred over Decimal. Adding support for Double to the Cast function makes it easier for expressions to continue to use Double.

How to repeat:
create table t as select 0.00;
insert into t 99.99;
select * from t;

Note, that the precision for the Decimal column (when using MySQL5) in t is Decimal(3,2). Values inserted into this table will be truncated by MySQL5, but not by MySQL4.

desc t;

Suggested fix:
Support: create table t as select Cast(0 as Double)
[20 Aug 2007 22:45] MySQL Verification Team 
Thank you for the bug report.
[19 May 2019 23:42] Jon Stephens 
Fixed in MySQL 8.0.17 by WL#529. Changelog entry as follows:

    MySQL now supports explicits casts to DOUBLE, FLOAT, and REAL using
    either of the functions CAST() or CONVERT(). For more information, see
    "Cast Functions and Operators" 
    [https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html].

Closed.

Note that the CREATE statement shown initially yields a DECIMAL(3,2) column. In order for the subsequent INSERT to work, use CREATE TABLE t AS SELECT 00.00 which produces a DECOMAL(4,2) column or CREATE TABLE AS SELECT CAST(0 AS DOUBLE) which makes the column explicitly DOUBLE.