Bug #4346 SUM() Function returns 0 instead of NULL in a case
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 |
+-----------+--------+
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.