MySQL Bugs: #28980: ROUND(x,y) returns different results for same literal and int column values as y

Bug #28980	ROUND(x,y) returns different results for same literal and int column values as y
Submitted:	8 Jun 2007 17:00	Modified:	25 Sep 2014 9:44
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S1 (Critical)
Version:	5.0.44-BK, 5.0.22	OS:	Linux
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
If one use an INTEGER column from a table to ROUND() a figure then he may get a different result to when fixed constant is used:

mysql> CREATE TABLE rounding (id int(10) unsigned NOT NULL auto_increment, dps
tinyint(3) unsigned default NULL, qty decimal(16,6) default NULL, PRIMARY KEY (
id) );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO rounding VALUES (0,3,1.1325);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT ROUND(qty, dps), ROUND(qty, 3), dps FROM rounding;
+-----------------+---------------+------+
| ROUND(qty, dps) | ROUND(qty, 3) | dps  |
+-----------------+---------------+------+
|        1.132000 |         1.133 |    3 |
+-----------------+---------------+------+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.44-debug |
+--------------+
1 row in set (0.01 sec)

How to repeat:
CREATE TABLE rounding (
  id int(10) unsigned NOT NULL auto_increment, 
  dps tinyint(3) unsigned default NULL, 
  qty decimal(16,6) default NULL, 
  PRIMARY KEY (id));
INSERT INTO rounding VALUES (0,3,1.1325);
SELECT ROUND(qty, dps), ROUND(qty, 3), dps FROM rounding;

Suggested fix:
ROUND consistently? Use proper data type?

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/28624

ChangeSet@1.2533, 2007-06-12 22:05:01-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #28980: the result of ROUND(<decimal expr>,<int column>) 
  was erroneously converted to double, while the result of
  ROUND(<decimal expr>, <int literal>) was preserved as decimal.
  As a result of such a conversion the value of ROUND(D,A) could
  differ from the value of ROUND(D,val(A)) if D was a decimal expression.
  
  Now the result of the ROUND function is never converted to 
  double if the first argument is decimal.

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/28683

ChangeSet@1.2533, 2007-06-13 09:32:36-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #28980: the result of ROUND(<decimal expr>,<int column>) 
  was erroneously converted to double, while the result of
  ROUND(<decimal expr>, <int literal>) was preserved as decimal.
  As a result of such a conversion the value of ROUND(D,A) could
  differ from the value of ROUND(D,val(A)) if D was a decimal expression.
  
  Now the result of the ROUND function is never converted to 
  double if the first argument is decimal.

Pushed into 5.0.44

Pushed into 5.1.20-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Added to the changelogs for 5.0.44 and 5.1.20-beta.

This bug still appears to exist in 5.5.37 and 5.6.20. When I run Valeriy Kravchuk's test case it produces the wrong output when passing the integer table column as the second param to ROUND().

Do you run it on Linux? I can not reproduce with 5.5.37 on Windows, for example:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE rounding (
    ->   id int(10) unsigned NOT NULL auto_increment,
    ->   dps tinyint(3) unsigned default NULL,
    ->   qty decimal(16,6) default NULL,
    ->   PRIMARY KEY (id));
Query OK, 0 rows affected (2.28 sec)

mysql> INSERT INTO rounding VALUES (0,3,1.1325);
Query OK, 1 row affected (0.23 sec)

mysql> SELECT ROUND(qty, dps), ROUND(qty, 3), dps FROM rounding;
+-----------------+---------------+------+
| ROUND(qty, dps) | ROUND(qty, 3) | dps  |
+-----------------+---------------+------+
|        1.133000 |         1.133 |    3 |
+-----------------+---------------+------+
1 row in set (0.24 sec)

My mistake. I was misreading the results. MySQL 5.5 and 5.6 does return:
+-----------------+---------------+------+
| ROUND(qty, dps) | ROUND(qty, 3) | dps  |
+-----------------+---------------+------+
|        1.133000 |         1.133 |    3 |
+-----------------+---------------+------+

I was tripped up by the scale of the results. The documentation says ROUND(X,D) rounds the argument X to D decimal places. Why does ROUND(qty, dps) return a result with 6 decimal places while ROUND(qty, 3) has 3 decimal places. Shouldn't they both return 1.113?