Description:
The SUM() function should return NULL if the return set has no rows. I found a case where this does not work with 3.23.48.
Following Statement:
SELECT parent.parent_id, SUM( child.value ) AS result FROM child RIGHT JOIN parent ON child.id_parent = parent.parent_id GROUP BY parent.parent_id;
returns (FALSE !):
+-----------+--------------+
| parent_id | result |
+-----------+--------------+
| 1 | 90 |
| 2 | 0 |
| 3 | 0 |
+-----------+--------------+
But following Statement:
SELECT SUM(value) FROM child WHERE 1=2 ;
returns:
+------------+
| SUM(value) |
+------------+
| NULL |
+------------+
Version 4.0.13
returns in Case 1 (RIGHT !):
+-----------+--------------+
| parent_id | result |
+-----------+--------------+
| 1 | 90 |
| 2 | NULL |
| 3 | NULL |
+-----------+--------------+
How to repeat:
CREATE TABLE parent (
parent_id int(11) NOT NULL auto_increment,
PRIMARY KEY (parent_id)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);
INSERT INTO parent VALUES (3);
CREATE TABLE child (
child_id int(11) NOT NULL auto_increment,
id_parent int(11) NOT NULL default '0',
value int(11) NOT NULL default '0',
PRIMARY KEY (child_id)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
INSERT INTO child VALUES (1, 1, 34);
INSERT INTO child VALUES (2, 1, 56);
SELECT parent.parent_id, SUM( child.value ) AS result FROM child RIGHT JOIN parent ON child.id_parent = parent.parent_id GROUP BY parent.arent_id;
Suggested fix:
SELECT parent.parent_id, IF( COUNT(child.value) = 0, 'NULL', SUM(child.value)) AS result FROM child RIGHT JOIN parent ON child.id_parent = parent.parent_id GROUP BY parent.parent_id;
returns (RIGHT !):
+-----------+--------+
| parent_id | result |
+-----------+--------+
| 1 | 90 |
| 2 | NULL |
| 3 | NULL |
+-----------+--------+
