Bug #27984 Long Decimal Maths produces truncated results
Submitted: 20 Apr 2007 14:06 Modified: 18 Jun 2007 19:41
Reporter: Giles McArdell Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.27/5.1 OS:Linux (Redhat)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: decimal, Math, truncate

[20 Apr 2007 14:06] Giles McArdell
Description:
Fairly simple maths on long decimal numbers produces odd 'truncated' results.

The example given produces two results, 'a' and 'b' which have identical inputs and should produce identical results, 'a' however has the first 10 digits of the result set to zero, 'b' is correct.

How to repeat:
SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b;

Suggested fix:
Fiddle about using brackets or different data types until the correct result is achieved.
[20 Apr 2007 14:32] Godofredo Miguel Solorzano
Thank you for the bug report.

[miguel@light 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.42-debug Source distribution

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

mysql> SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 *
    -> (99.999999999998 / 100) b;
+--------------------------------+--------------------------------+
| a                              | b                              |
+--------------------------------+--------------------------------+
| 0.0000000009999800000000000000 | 0.9999999999999800000000000000 | 
+--------------------------------+--------------------------------+
1 row in set (0.02 sec)

[miguel@light 4.1]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.23-debug

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

mysql> SELECT 1.000000000000 * 99.999999999998 / 100 a,1.000000000000 * (99.999999999998 / 100) b;
+------------------+------------------+
| a                | b                |
+------------------+------------------+
| 0.99999999999998 | 0.99999999999998 |
+------------------+------------------+
1 row in set (0.01 sec)
[19 May 2007 21:06] 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/27034

ChangeSet@1.2490, 2007-05-20 00:34:53+05:00, holyfoot@mysql.com +3 -0
  Bug #27984 Long Decimal Maths produces truncated results
  decimal_round failed to round the number starting
  with nine 9-s, setting first nine digits of the decimal to zero.
[21 May 2007 15:15] 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/27077

ChangeSet@1.2490, 2007-05-21 19:15:35+05:00, holyfoot@mysql.com +3 -0
  Bug #27984 Long Decimal Maths produces truncated results
  decimal_round failed to perform a correct rounding 
  of a decimal number if its first nine digits were '9'.
  It just sets those digits to 0.
[21 May 2007 18:23] 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/27090

ChangeSet@1.2490, 2007-05-21 22:22:47+05:00, holyfoot@mysql.com +3 -0
  Bug #27984 Long Decimal Maths produces truncated results.
  decimal_round failed to perform a correct rounding 
  of a decimal number if its first nine digits were '9'.
  It just sets those digits to 0.
[4 Jun 2007 21:21] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:23] Bugs System
Pushed into 5.0.44
[18 Jun 2007 19:41] Paul Dubois
Noted in 5.0.44, 5.1.20 changelogs.

DECIMAL values beginning with nine 9 digits could be incorrectly
rounded.