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 | |
+----------+---------------+---------------+