Bug #4346 SUM() Function returns 0 instead of NULL in a case

Submitted: 30 Jun 2004 17:17 Modified: 30 Jun 2004 18:00
Reporter: Torsten Zachert Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:3.23.48-Max OS:Linux (Suse Linux)
Assigned to: Dean Ellis CPU Architecture:Any

[30 Jun 2004 17:17] Torsten Zachert
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   |
+-----------+--------+
[30 Jun 2004 18:00] Dean Ellis
I can verify that this occurs even against the last 3.23 release (3.23.58), however 4.0 forward does return NULL, and 3.23 is receiving only very critical bug fixes now.

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.