MySQL Bugs: #9501: Arbitrary precision math done incorretly (numeric math in fp maybe?)

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

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.

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

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