| Bug #36270 | incorrect calculation result - works in 4.1 but not in 5.0 or 5.1 | ||
|---|---|---|---|
| Submitted: | 23 Apr 2:52 | Modified: | 21 Aug 20:15 |
| Reporter: | Ben Krug | ||
| Status: | Documenting | ||
| Category: | Server | Severity: | S2 (Serious) |
| Version: | 5.0.58, 5.1.24 | OS: | Any |
| Assigned to: | Tatjana A. Nuernberg | Target Version: | 5.0+ |
| Tags: | precision, floating-point, Rounding | ||
| Triage: | D2 (Serious) | ||
[23 Apr 2:52]
Ben Krug
[23 Apr 6: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 18:07]
Valeriy Kravchuk
Bug #36318 was marked as a duplicate.
[13 May 14:31]
Tatjana A. 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 14:34]
Tatjana A. Nuernberg
(different result in 4.1 due to different types / totally different code-path)
[13 May 14: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 16:03]
Sveta Smirnova
Bug #37443 was marked as duplicate of this one.
[15 Aug 21: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 22:05]
Chad MILLER
Queued to 5.0-bugteam, 5.1-bugteam, and 6.0-bugteam.
[15 Aug 22: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 20:15]
Bugs System
Pushed into 5.1.28 (revid:chad@mysql.com-20080815194621-w9qpjn75g0jyozjm) (version source revid:sergefp@mysql.com-20080819132519-eimtstp3bx89ya9d) (pib:3)
