MySQL Bugs: #45860: significant inaccuracy in decimal multiplication calculations

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:	None
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

Description:
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)

Thanks.

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);

select  
((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 ;

Thank you for the report.

Verified as described.

Is not repeatable with version 4.1.

There is similar bug #36270, but latter is already fixed.

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.

Noted in 5.5.30, 5.6.10, 5.7.1 changelogs.

DECIMAL multiplication operations could produce significant
inaccuracy.