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:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0 OS:
Assigned to: Alexey Botchkov

[24 Oct 2005 17:26] Matthias Leich
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);
[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.