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:	31 Mar 2005 0:08	Modified:	31 Mar 2005 17:45
Reporter:	Scott Marlowe
Status:	Closed
Category:	Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.3	OS:	Linux (Fedora Core 2)
Assigned to:	Bugs System	Target Version:

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