MySQL Bugs: #27984: Long Decimal Maths produces truncated results

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

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.

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)

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.

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.

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.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.

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