Bug #34369 Casting a float to a decimal returns wrong result

• Submitted: 7 Feb 2008 1:29
• Modified: 7 Feb 2008 4:28
• Reporter: Baron Schwartz (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0.45
• OS: Any
• CPU Architecture: Any
• Tags: qc

[7 Feb 2008 1:29] Baron Schwartz

Description:
Casting a float to a decimal with the same number of digits in M and D results in 0.99999999999.....

How to repeat:
mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(a float);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t(a) values(2.2);
Query OK, 1 row affected (0.00 sec)

mysql> select cast(a as decimal(16,16)) from t;
+---------------------------+
| cast(a as decimal(16,16)) |
+---------------------------+
|        0.9999999999999999 | 
+---------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select cast(a as decimal(17,16)) from t;
+---------------------------+
| cast(a as decimal(17,16)) |
+---------------------------+
|        2.2000000476837160 | 
+---------------------------+
1 row in set (0.00 sec)

mysql> select cast(a as decimal(17,17)) from t;
+---------------------------+
| cast(a as decimal(17,17)) |
+---------------------------+
|       0.99999999999999999 | 
+---------------------------+
1 row in set, 1 warning (0.00 sec)

[7 Feb 2008 4:28] Valeriy Kravchuk

Thank you for a problem report. I was able to repeat the behaviour described, but I think this is not a bug. 

If you cast to DECIMAL(N,N), the largest value you can represent by this type is .999999... where number of 9s is N. The value you cast (2.2) is bigger, so as usual for MySQL it is converted to largest possible and you get a warning. Like in this case:

mysql> select cast(1000 as decimal(3));
+--------------------------+
| cast(1000 as decimal(3)) |
+--------------------------+
|                      999 |
+--------------------------+
1 row in set, 1 warning (0.00 sec)