Bug #27984 Long Decimal Maths produces truncated results
Submitted: 20 Apr 2007 16:06 Modified: 18 Jun 2007 21:41
Reporter: Giles McArdell
Status: Closed
Category:Server: Types Severity:S2 (Serious)
Version:5.0.27/5.1 OS:Linux (Redhat)
Assigned to: Alexey Botchkov Target Version:
Tags: truncate, Math, decimal

[20 Apr 2007 16: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 16:32] 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 23: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 17: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 20: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 23:21] Bugs System 
Pushed into 5.1.20-beta
[4 Jun 2007 23:23] Bugs System 
Pushed into 5.0.44
[18 Jun 2007 21:41] Paul DuBois 
Noted in 5.0.44, 5.1.20 changelogs.

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