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));
