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
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.23-BK, 5.0.22
• OS: Linux (Linux)
• Assigned to: Georgi Kodinov
• CPU Architecture: Any

[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] Valeriy 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.