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 19:00	Modified:	15 Jun 2007 15:32
Reporter:	Valeriy Kravchuk
Status:	Closed
Category:	Server: Types	Severity:	S1 (Critical)
Version:	5.0.44-BK, 5.0.22	OS:	Linux
Assigned to:	Igor Babaev	Target Version:

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.