MySQL Bugs: #36270: incorrect calculation result - works in 4.1 but not in 5.0 or 5.1

Bug #36270	incorrect calculation result - works in 4.1 but not in 5.0 or 5.1
Submitted:	23 Apr 2008 0:52	Modified:	11 Dec 2008 14:12
Reporter:	Ben Krug	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.58, 5.1.24	OS:	Any
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any
Tags:	floating-point, precision, Rounding

Description:
Not sure whether this is a floating-point precision issue or just a bug.  The following query returns the correct result in 4.1 but not in 5.0 or 5.1:

select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000);

returns 0.81298807 in 4.1 (correctly), but 0 in 5.0 or 5.1.

Is this floating-point rounding error, or a bug?  

More details:

If you take out any of the numbers, you get the correct result for the remaining numbers.  (Ie, you need all 8 numbers to get the 0 error.)

If you take off the trailing 0's on some of the numbers (eg, the last two), you get the correct result.

If you cast the first two numbers to, eg, DECIMAL(9,7), you get the correct result.

I just am not clear whether this is a rounding error or a bug.

How to repeat:
select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000);

in 4.1 and 5.0 or 5.1

Thank you for a bug report. Verified on 5.1.24 and 5.0.58:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot test -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.24-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
1.01500000 *
    -> 1.01500000 * 0.99500000)\G
*************************** 1. row ***************************
(1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 *
1.01500000 * 0.99500000): 0.000000000000000000000000000000
1 row in set (0.00 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.58-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
1.01500000 *
    -> 1.01500000 * 0.99500000)\G
*************************** 1. row ***************************
(1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 *
1.01500000 * 0.99500000): 0.000000000000000000000000000000
1 row in set (0.02 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 4.1.22-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 *
1.01500000 *
    -> 1.01500000 * 0.99500000)\G
*************************** 1. row ***************************
(1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 *
1.01500000 * 0.99500000): 0.81298807
1 row in set (0.03 sec)

And, indeed, removing all trailing zeros lead to correct result in 5.x.

Bug #36318 was marked as a duplicate.

For the example

select (1.20396873 * 0.89550000 * 0.68000000 * 1.08721696 * 0.99500000 * 1.01500000 * 1.01500000 * 0.99500000);

we get a progression of the type

$1 = 0xf84f40 "1.20396873"
$2 = 0xf84f40 "0.89550000"
=> "1.0781539977150000"

(gdb) print print_decimal(from1)
$5 = 0xf84f40 "1.0781539977150000"
(gdb) print print_decimal(from2)
$6 = 0xf84f40 "0.68000000"
(gdb) print print_decimal(to)
$7 = 0xf84f40 "0.7331447184462"

(gdb) print print_decimal(from1)
$8 = 0xf84f40 "0.7331447184462", '0' <repeats 11 times>
(gdb) print print_decimal(from2)
$9 = 0xf84f40 "1.08721696"
(gdb) print print_decimal(to)
$10 = 0xf84f40 "0.797087372029133487552",

(gdb) print print_decimal(from1)
$11 = 0xf84f40 "0.797087372029133487552", '0' <repeats 11 times>
(gdb) print print_decimal(from2)
$12 = 0xf84f40 "0.99500000"
(gdb) print print_decimal(to)
$13 = 0xf84f40 "0.79310193516898782011424", '0' <repeats 17 times>

(gdb) print print_decimal(from1)
$14 = 0xf84f40 "0.79310193516898782011424", '0' <repeats 17 times>
(gdb) print print_decimal(from2)
$15 = 0xf84f40 "1.01500000"
(gdb) print print_decimal(to)
$16 = 0xf84f40 "0.8049984641965226374159536", '0' <repeats 23 times>

(gdb) print print_decimal(from1)
$17 = 0xf84f40 "0.8049984641965226374159536", '0' <repeats 23 times>
(gdb) print print_decimal(from2)
$18 = 0xf84f40 "1.01500000"
(gdb) print print_decimal(to)
$19 = 0xf84f40 "0.817073441159470476977192904", '0' <repeats 29 times>

(gdb) print print_decimal(from1)
$20 = 0xf84f40 "0.817073441159470476977192904", '0' <repeats 29 times>
(gdb) print print_decimal(from2)
$21 = 0xf84f40 "0.99500000"
(gdb) print print_decimal(to)
$22 = 0xf84f40 "0.", '0' <repeats 63 times>

In other words:

- the scale grows with each multiplication, as a) expected and b) per the standard ("DECIMAL specifies the data type exact numeric ...  If the declared type of both operands of a dyadic arithmetic operator is exact numeric, then the declared type of the result is an implementation-defined exact numeric type ... The precision of the result of multiplication is implementation-defined, and the scale is S1 + S2.")

- after a sufficient number of multiplications, the scale will exceed our limits; a truncation will be necessary

- in the example, the less precise operand (0.995) is bounded and ends up being 0, hence the result ends up being zero.

Possible solution: when truncating, truncate the *longer* scale (that is, remove the *less* significant digits):

int decimal_mul(decimal_t *from1, decimal_t *from2, decimal_t *to)
{
...
  if (unlikely(error))
  {
    set_if_smaller(to->frac, frac0*DIG_PER_DEC1);
    set_if_smaller(to->intg, intg0*DIG_PER_DEC1);
    if (unlikely(i > intg0))                     /* bounded integer-part */
...
    else                                         /* bounded fract part */
    {
      j-=frac0;                                  /* correct by ... buf-chars */
      i=j >> 1;
      if (frac1 <= frac2)
      {
        frac1-= i;
        frac2-=j-i;
      }
      else
      {
        frac2-= i;
        frac1-=j-i;
      }
    }
  }

(different result in 4.1 due to different types / totally different code-path)

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/commits/46651

ChangeSet@1.2610, 2008-05-13 14:45:50+02:00, tnurnberg@noir.wlan.koehntopp.de +3 -0
  Bug#36270: incorrect calculation result - works in 4.1 but not in 5.0 or 5.1
  
  When the fractional part in a multiplication of DECIMALs
  overflowed, we truncated the first operand rather than the
  longest. Now truncating least significant places instead
  for more precise multiplications.

Bug #37443 was marked as duplicate of this one.

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/commits/51773

2664 Chad MILLER	2008-08-15
      Bug#36270: incorrect calculation result - works in 4.1 but not in 5.0 or 5.1
      
      When the fractional part in a multiplication of DECIMALs
      overflowed, we truncated the first operand rather than the
      longest. Now truncating least significant places instead
      for more precise multiplications.
      
      (Queuing at demand of Trudy/Davi.)

Queued to 5.0-bugteam, 5.1-bugteam, and 6.0-bugteam.

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/commits/51780

2664 Chad MILLER	2008-08-15
      Bug#36270: incorrect calculation result - works in 4.1 but not in 5.0 or 5.1
      
      When the fractional part in a multiplication of DECIMALs
      overflowed, we truncated the first operand rather than the
      longest. Now truncating least significant places instead
      for more precise multiplications.
      
      (Queuing at demand of Trudy/Davi.)

Pushed into 5.1.28  (revid:chad@mysql.com-20080815194621-w9qpjn75g0jyozjm) (version source revid:sergefp@mysql.com-20080819132519-eimtstp3bx89ya9d) (pib:3)

Noted in 5.1.28 changelog.

When the fractional part in a multiplication of DECIMAL values
overflowed, the server truncated the first operand rather than the
longest. Now the server truncates so as to produce more precise
multiplications.

Setting report to NDI pending push into 5.0.x, 6.0.x.

Pushed into 6.0.7-alpha  (revid:chad@mysql.com-20080815194621-w9qpjn75g0jyozjm) (version source revid:sven@mysql.com-20080818175803-c1nutd5773r6b4gd) (pib:3)

Noted in 6.0.7 changelog.

Setting report to NDI pending push into 5.0.x.

Noted in 5.0.70 changelog.