MySQL Bugs: #38635: Round() function not working under some circumstances

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:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	double, myisam, round

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`

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

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);

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.