Bug #9501 Arbitrary precision math done incorretly (numeric math in fp maybe?)
Submitted: 30 Mar 2005 22:08 Modified: 31 Mar 2005 15:45
Reporter: Scott Marlowe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.3 OS:Linux (Fedora Core 2)
Assigned to: Sergei Golubchik CPU Architecture:Any

[30 Mar 2005 22:08] Scott Marlowe
Description:
Currently, all versions of MySQL appear to do math on arbitrarily long numbers with floating point or some other library apparently not built for the job.

Note that my first test is being done in double precision floating point.  Numeric types should be operated on by libraries handling arbitrarily long numeric strings, not any fixed width based lib like floating point, unless you're willing to use some kind of REALLY wide one, that can handle the width of your numeric range without loss of precision.

If you aren't sure how to handle such things, please take a look at:

http://www.shoup.net/ntl/ and a good maths text.

How to repeat:
Here's a simple test case:

This gets the right answer:

create table num2 (i1 double, i2 double);
insert into num2 values (10.333,12.345);
select i1/i2 from num2;

0.83701903604698

But this gets the wrong answer;

create table num (i1 numeric(64,60), i2 numeric(64,60));
insert into num values (10.333,12.345);
select i1/i2 from num;
0.83701903600000000004698258404212231672742000810044552450384771162

Suggested fix:
Note that my first test is being done in double precision floating point.  Numeric types should be operated on by libraries handling arbitrarily long numeric strings, not any fixed width based lib like floating point, unless you're willing to use some kind of REALLY wide one, that can handle the width of your numeric range without loss of precision.

If you aren't sure how to handle such things, please take a look at:

http://www.shoup.net/ntl/ and a good maths text.
[31 Mar 2005 15:39] 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/23532
[31 Mar 2005 15:45] Sergei Golubchik
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:

Fixed in 5.0.4