Bug #45291 CAST from DOUBLE to DECIMAL with incorrect results
Submitted: 3 Jun 2009 9:57 Modified: 23 Jun 2009 6:39
Reporter: Daniel Fischer Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0, 5.1 bzr OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[3 Jun 2009 9:57] Daniel Fischer
Description:
A CAST from a DOUBLE literal to a DECIMAL type results in incorrect results for large DOUBLE values.

mysql> select cast(1e+60 as decimal(64));
+--------------------------------------------------------------+
| cast(1e+60 as decimal(64))                                   |
+--------------------------------------------------------------+
| 999999999999999900000000000000000000000000000000000000000000 | 
+--------------------------------------------------------------+
1 row in set (0.00 sec)

This can also be observed with large negative numbers starting with -1e+60.

Example of correct behaviour:

mysql> select cast(1e+59 as decimal(64));
+--------------------------------------------------------------+
| cast(1e+59 as decimal(64))                                   |
+--------------------------------------------------------------+
| 100000000000000000000000000000000000000000000000000000000000 | 
+--------------------------------------------------------------+
1 row in set (0.00 sec)

Example of correct behaviour, reporting an error, for a DOUBLE value that is larger than the target type can accomodate:

mysql> select cast(1e+65 as decimal(64));
+------------------------------------------------------------------+
| cast(1e+65 as decimal(64))                                       |
+------------------------------------------------------------------+
| 9999999999999999999999999999999999999999999999999999999999999999 | 
+------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------+
| Level | Code | Message                                                                      |
+-------+------+------------------------------------------------------------------------------+
| Error | 1264 | Out of range value adjusted for column 'cast(1e+65 as decimal(64))' at row 1 | 
+-------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
SELECT CAST(1e+60 AS DECIMAL(64));
[3 Jun 2009 10:57] Sveta Smirnova
Thank you for the report.

Verified as described. Problem does not exists in version 6.0, although I can not find similar bug report.
[23 Jun 2009 6:40] Omer Barnir
This issue is addressed in a future release and has a good workaround (W4 - double cast - cast(cast(1e+60 as char) as decimal(64)) )