Bug #38635 Round() function not working under some circumstances
Submitted: 7 Aug 2008 17:40 Modified: 7 Aug 2008 21:02
Reporter: Andrea Nuñez Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.51b OS:Microsoft Windows (XP)
Assigned to: CPU Architecture:Any
Tags: double, myisam, round

[7 Aug 2008 17:40] Andrea Nuñez
Description:
I' ve a table like that

CREATE TABLE `test` (
  `c1` double NOT NULL,
  PRIMARY KEY  (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert INTO `test`(`c1`) values (1327.5);

And if I do "Select round(c1) as myC1 from `test` " the result give me 1327 
so it is roundering down

How to repeat:
CREATE TABLE `test` (
  `c1` double NOT NULL,
  PRIMARY KEY  (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert INTO `test`(`c1`) values (1327.5);

Run the next sql:

Select round(c1) as myC1 from `test`
[7 Aug 2008 21:02] Sveta Smirnova
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #15936
[9 Feb 2009 11:54] Maxim Andruhovich
It`s not a duplicate.

Version 5.1.30, WinXP
When you run simple query like SELECT ROUND(6.655, 2);
everything looks fine, the result is 6.66.

But if you run a little bit complex query like
SELECT ROUND( SUM( rd_sum ) , 2 )
FROM roundtest;

Than result is wrong! And for now it is 6.65

For the test create table and data:
DROP TABLE IF EXISTS `roundtest`;
CREATE TABLE IF NOT EXISTS `roundtest` (
`rd_id` bigint(255) unsigned NOT NULL AUTO_INCREMENT,
`rd_sum` double(251,4) unsigned NOT NULL,
PRIMARY KEY (`rd_id`),
KEY `rd_sum` (`rd_sum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `roundtest` (`rd_id`, `rd_sum`) VALUES
(1, 3.2820),
(2, 3.3730);
[9 Feb 2009 17:13] Sveta Smirnova
Maxim,

bug #15936 is about different behavior of ROUND on Windows and Unix. On Unix your example works as you expect:

mysql> INSERT INTO `roundtest` (`rd_id`, `rd_sum`) VALUES
    -> (1, 3.2820),
    -> (2, 3.3730);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT ROUND( SUM( rd_sum ) , 2 )
    -> FROM roundtest;
+----------------------------+
| ROUND( SUM( rd_sum ) , 2 ) |
+----------------------------+
|                       6.66 | 
+----------------------------+
1 row in set (0.00 sec)

So this is still duplicate.