Bug #37624 query produces incorrect results witch float values
Submitted: 25 Jun 2008 10:26 Modified: 25 Jun 2008 11:39
Reporter: Dominik Winter Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: FLOAT, join, MAX, null

[25 Jun 2008 10:26] Dominik Winter
Description:
The query returns wrong (rounded?) values with a combination of aggregate functions and null.

How to repeat:
CREATE TABLE `user` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `username` VARCHAR( 32 ) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE = MYISAM;

  
INSERT INTO user (`id`, `username`) VALUES(NULL, 'shoan');
INSERT INTO user (`id`, `username`) VALUES(NULL, 'test');

CREATE TABLE `game` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `user_id` INT UNSIGNED NOT NULL,
   `month` TINYINT NOT NULL,
   `points` FLOAT NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY ukey (`user_id`,`month`)
) ENGINE = MYISAM;

INSERT INTO game (`id`, `user_id`, `month`, `points`) VALUES(NULL, 1, 1, 10);
INSERT INTO game (`id`, `user_id`, `month`, `points`) VALUES(NULL, 1, 2, 12.7);
INSERT INTO game (`id`, `user_id`, `month`, `points`) VALUES(NULL, 2, 1, 9);

SELECT
   user.username,
   MAX(IF(game.month = 1, game.points, NULL))   AS 'points_month1',
   MAX(IF(game.month = 2, game.points, NULL))   AS 'points_month2'
FROM user
   LEFT JOIN game ON game.user_id = user.id
GROUP BY user.id;

+----------+---------------+------------------+
| username | points_month1 | points_month2    |
+----------+---------------+------------------+
| shoan    |            10 | 12.6999998092651 | <-- should be 12.7
| test     |             9 |             NULL |
+----------+---------------+------------------+

Suggested fix:
If I use ticks instead of null, it works correctly.

SELECT
   user.username,
   MAX(IF(game.month = 1, game.points, ''))   AS 'points_month1',
   MAX(IF(game.month = 2, game.points, ''))   AS 'points_month2'
FROM user
   LEFT JOIN game ON game.user_id = user.id
GROUP BY user.id;

+----------+---------------+---------------+
| username | points_month1 | points_month2 |
+----------+---------------+---------------+
| shoan    | 10            | 12.7          |
| test     | 9             |               |
+----------+---------------+---------------+
[25 Jun 2008 11:39] MySQL Verification Team
Thank you for the bug report. Please read: http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html