| Bug #14268 | CAST or assign FLOAT/DOUBLE to DECIMAL leads to wrong values | ||
|---|---|---|---|
| Submitted: | 24 Oct 2005 17:26 | Modified: | 23 Nov 2005 13:46 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
[24 Oct 2005 17:28]
Matthias Leich
test script
Attachment: ml036.test (application/test, text), 961 bytes.
[24 Oct 2005 17:28]
Matthias Leich
test results
Attachment: ml036.reject (application/octet-stream, text), 18.98 KiB.
[3 Nov 2005 9:33]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/31861
[23 Nov 2005 13:46]
Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Documented fix in 5.0.17 changelog.
Description: CAST(<column of data type FLOAT/DOUBLE> AS DECIMAL(65,30)) and SET <column type decimal> = <column type float/double> gives under NOT rare circumstances terrible wrong values. Example: CREATE TABLE t1 ( my_float FLOAT, my_double DOUBLE, my_varchar VARCHAR(50), my_decimal DECIMAL(65,30) ); The table contains values in the style of SELECT my_float, my_double, my_varchar FROM t1; my_float my_double my_varchar 1.17549e-32 1.175494345e-32 1.175494345e-32 1.17549e-31 1.175494345e-31 1.175494345e-31 .... ..... ...... Example of result set with partially wrong values: SELECT CAST(my_float AS DECIMAL(65,30)), my_float FROM t1; CAST(my_float AS DECIMAL(65,30)) my_float 0.000000000000000000000000000000 1.17549e-32 (1) 0.000000000000000000000000000000 1.17549e-31 (1) 0.000000000000000000000000000000 1.17549e-30 (2*) 0.000000000000000000000000000000 1.17549e-29 (2) .... 0.000000000000000000000000000000 1.17549e-08 (2) 0.000000000000000000000000000000 1.17549e-07 (2) 0.000001000000000000000000000000 1.17549e-06 (2**) 0.000012000000000000000000000000 1.17549e-05 (2**) 0.000118000000000000000000000000 0.000117549 (2**) 0.001175000000000000000000000000 0.00117549 (2**) 0.011755000000000000000000000000 0.0117549 (2**) 0.117549000000000000000000000000 0.117549 (1) correct value for CAST ... (2) This CAST ... value is definetely wrong. (2*) Maybe this could be called acceptable because of rounding/truncating ... (2**) We a value around the expected one, but the precision is too low. I assume in general CAST ... AS DECIMAL(65,30) gives wrong output whenever the input value is < 1e-6. Any UPDATE t1 SET my_decimal = my_float or UPDATE t1 SET my_decimal = my_double stores the same wrong values into the column my_decimal. I guess this is because the update is transformed to UPDATE t1 SET my_decimal = CAST(<source column> AS decimal(65,30)) Workaround: UPDATE t1 SET my_decimal = FORMAT(my_float,30); instead of SET my_decimal = my_float Attention: CAST(FORMAT(my_float,30) AS DECIMAL(65,30)) gives garbage and is NO workaround My environment: - Intel PC with Linux(SuSE 9.3) - MySQL compiled from source Version ChangeSet@1.2025.1.2, 2005-10-21 How to repeat: Please use my attached testscript ml036.test copy it to mysql-test/t echo "Dummy" > r/ml036.result # Produce a dummy file with # expected results ./mysql-test-run ml036 inspect r/ml036.reject # The protocol of the execution. Workaround: UPDATE t1 SET my_decimal = FORMAT(my_float,30);