Bug #20569 Garbage in DECIMAL results from some mathematical functions
Submitted: 20 Jun 2006 11:15 Modified: 24 Jul 2006 17:45
Reporter: Giles McArdell Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.23-BK, 5.0.22 OS:Linux (Linux)
Assigned to: Georgi Kodinov

[20 Jun 2006 11:15] Giles McArdell
Description:
With some values and functions, the result has garbage after the number.

The example gives '0.505974818-,+'(+(.0524000000000'

0.505974818 is the correct result (albeit truncated to 9 decimal places), the rest looks like an overflow into memory.

The input values seem significant, swapping the sign round, or making the values the same sign, gets rid of the garbage.

How to repeat:
SELECT ( ( ( 1 + (-1.091147916/100) )*(1 + (1.6147423911/100) ) ) - 1)*100 c;

Suggested fix:
If accuracy is not required it would be possible to use ROUND(,) to get just the numeric part (tested and it works, odd that round does not complain about the garbage characters in the number).
This is hardly ideal however.
[20 Jun 2006 11:52] Valerii Kravchuk
Thank you for a bug report. Verified just as described with 5.0.23-BK on Linux:

mysql> SELECT (((1 + (1.091147916/100))*(1 + (-1.6147423911/100))) - 1)*100 c;
+--------------------------------+
| c                              |
+--------------------------------+
| -0.541213703049256219476000000 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT (((1 + (-1.091147916/100))*(1 + (-1.6147423911/100))) - 1)*100 c;

+--------------------------------+
| c                              |
+--------------------------------+
| -2.688271079150743780524000000 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT (((1 + (-1.091147916/100))*(1 + (1.6147423911/100))) - 1)*100 c;
+-------------------------------+
| c                             |
+-------------------------------+
| 0.505974818-,+'(+(.0524000000 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.23    |
+-----------+
1 row in set (0.00 sec)
[27 Jun 2006 9:06] Giles McArdell
Another Workaround:
If the results of either of the source numbers in the inner multiplication is rounded to 9 decimal places then the garbage is removed, IE:

SELECT 	( ( ROUND( 1 + (-1.091147916/100),9 )*(1 + (1.6147423911/100) ) ) - 1)*100 c;

The result is still not correct but it is close enough for many applications.
[3 Jul 2006 13:24] Colin McFarlane
We've encountered this bug as well and it's fairly critical for us. Is there an estimate for a fix?
[5 Jul 2006 13:38] 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/8765
[5 Jul 2006 15:51] 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/8781
[7 Jul 2006 14:27] 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/8912
[11 Jul 2006 9:47] Giles McArdell
Thank you for the fast response.

I have re-built my MySQL test installation with the fix in place and the bug is now gone.

So it`s back to testing for me.....
[19 Jul 2006 0:02] Evgeny Potemkin
Adding decimal "digits" in multiplication resulted in signed overflow and
producing wrong results.

Fixed in 5.0.25
[24 Jul 2006 17:45] Paul Dubois
Noted in 5.0.25 changelog.

Multiplication of DECIMAL values could produce incorrect fractional
part and trailing garbage caused by signed overflow.