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
Triage: D2 (Serious)

[23 Apr 2008 0:52] Ben Krug
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
[23 Apr 2008 4:04] Valeriy Kravchuk
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.
[24 Apr 2008 16:07] Valeriy Kravchuk
Bug #36318 was marked as a duplicate.
[13 May 2008 12:31] Tatiana Azundris Nuernberg
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;
      }
    }
  }
[13 May 2008 12:34] Tatiana Azundris Nuernberg
(different result in 4.1 due to different types / totally different code-path)
[13 May 2008 12:47] Bugs System
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.
[17 Jun 2008 14:03] Sveta Smirnova
Bug #37443 was marked as duplicate of this one.
[15 Aug 2008 19:46] Bugs System
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.)
[15 Aug 2008 20:05] Chad MILLER
Queued to 5.0-bugteam, 5.1-bugteam, and 6.0-bugteam.
[15 Aug 2008 20:07] Bugs System
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.)
[21 Aug 2008 18:15] Bugs System
Pushed into 5.1.28  (revid:chad@mysql.com-20080815194621-w9qpjn75g0jyozjm) (version source revid:sergefp@mysql.com-20080819132519-eimtstp3bx89ya9d) (pib:3)
[27 Aug 2008 2:06] Paul Dubois
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.
[14 Sep 2008 2:47] Bugs System
Pushed into 6.0.7-alpha  (revid:chad@mysql.com-20080815194621-w9qpjn75g0jyozjm) (version source revid:sven@mysql.com-20080818175803-c1nutd5773r6b4gd) (pib:3)
[15 Sep 2008 19:05] Paul Dubois
Noted in 6.0.7 changelog.

Setting report to NDI pending push into 5.0.x.
[11 Dec 2008 14:12] Paul Dubois
Noted in 5.0.70 changelog.