Bug #45860 significant inaccuracy in decimal multiplication calculations
Submitted: 30 Jun 2009 16:31 Modified: 16 Jan 2013 19:57
Reporter: michael fairbank Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.31-1ubuntu2, 5.0, 5.1, 5.4 bzr OS:Linux (ubuntu 9.04 64 bit, linux kernel 2.6.28-13-generic)
Assigned to: CPU Architecture:Any
Tags: Decimal accuracy calculations, regression

[30 Jun 2009 16:31] michael fairbank
The script attached creates a table and runs a SELECT statement on it.  The select statement should produces two identical results, since the two expressions are algebraically equivalent.  However they are different by a huge amount (a size of error I would not expect due to usual floating point error).

The two values produced by my implementation of MYSQL are:
5.451325655464380184732125016805 and 3.115043231693931534132642866746

These should be the same (approximately), but clearly aren't.

This is a very strange bug - altering the corresponding numbers on the two lines just slightly often makes the error disappear.

I am using Linux, AMD 64 bit (ubuntu 9.04 64 bit, linux kernel 2.6.28-13-generic)


Client program being used:
MySQL Client Version 5.0.75.
Queries run in  Mysql query browser version 1.2.12, by MySQL AB.

How to repeat:

create table temp1
(a int not null, 
b decimal(26,24) not null,
c decimal(26,24) not null,
d decimal(26,24) not null, primary key (a));

INSERT INTO temp1 (a, b,c,d) values(10,0.006968641114982301,-0.004401247019988831,4.514099507680858E-4);

((b*4000*1.0/4.0*(c+(d*4000)/4.0))*4096.0)*1.75/4096.0 e,
((b*4000*1.0/4.0*(c+(d*4000)/4.0))       )*1.75 f
from temp1 
where a=10 ;
[1 Jul 2009 6:04] Sveta Smirnova
Thank you for the report.

Verified as described.
[1 Jul 2009 6:09] Sveta Smirnova
Is not repeatable with version 4.1.

There is similar bug #36270, but latter is already fixed.
[26 Nov 2012 11:26] Tor Didriksen
frac is the number of decimal digits after the point
For each multiplication in the expression, decimal_mul() does this:
  to->frac= from1->frac + from2->frac;              /* store size in digits */
which will eventually overflow.
The code for handling the overflow, will truncate the two digits in "1.75" to "1"
It would clearly be better to strip off some
of the 72 digits of the other part of the expression.
[16 Jan 2013 19:57] Paul DuBois
Noted in 5.5.30, 5.6.10, 5.7.1 changelogs.

DECIMAL multiplication operations could produce significant