Bug #732 left join & SUM() not work as expected.
Submitted: 26 Jun 2003 7:16 Modified: 3 Jul 2003 15:34
Reporter: Zoltán Kaposi Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.13 OS:Windows (windows)
Assigned to: Michael Widenius CPU Architecture:Any

[26 Jun 2003 7:16] Zoltán Kaposi
Description:
The SUM() function does not work properly on a left joined column, if no matching field in the left joined table.

My query:

select products.internal_item, products.ident, products.item_name,
sum(tracking.quantity) as sum_all,
sum(tracking.quantity) - sum(reserved.quantity) as sum_free
from products, tracking

left join reserved on
tracking.track_id = reserved.track_id
where products.supply = 10000003
and tracking.internal_item=products.internal_item
and tracking.move = 2
and tracking.quantity > 0
group by products.internal_item

The "sum_free" field will be NULL, instead of sum(tracking-quantity)-0 if no corresponding row in "reserved".
If you run my sample, the bug will the second row in the result: "sum_free" will be NULL, instead of 10.

In the earlier versions since about 3.23.32 this query type worked fine, the "sum_free" has had the good value. The 4.0.12 works fine too.

How to repeat:
There are the tables and data for reproducing the bug (use the query in the above box)

CREATE TABLE products (
  internal_item mediumint(8) unsigned NOT NULL auto_increment,
  ident char(20) default NULL,
  item_name char(50) default NULL,
  supply mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (internal_item)
) TYPE=MyISAM;

INSERT INTO products VALUES("9224", "36526000", "Recta compass", "10000003");
INSERT INTO products VALUES("9225", "36527000", "Alpin compass", "10000003");
INSERT INTO products VALUES("9226", "36528400", "Height sensor", "10000003");
INSERT INTO products VALUES("9227", "36529000", "M1 compass", "10000003");

CREATE TABLE reserved (
  conn_id mediumint(8) unsigned NOT NULL default '0',
  track_id mediumint(8) unsigned default NULL,
  user varchar(30) default NULL,
  timeout datetime default NULL,
  quantity decimal(7,2) NOT NULL default '0.00',
  replaceable enum('T','F') NOT NULL default 'F',
  UNIQUE KEY prim (conn_id,track_id)
) TYPE=MyISAM COMMENT='reserved items';

INSERT INTO reserved VALUES("1", "10000001", "root@192.168.10.200", "2003-06-26 15:20:21", "5.00", "F");

CREATE TABLE tracking (
  track_id mediumint(8) unsigned NOT NULL auto_increment,
  track_before mediumint(8) unsigned NOT NULL default '0',
  move tinyint(3) unsigned default NULL,
  supply mediumint(8) unsigned default '0',
  internal_item smallint(5) unsigned default NULL,
  quantity decimal(7,2) default NULL,
  inv_id mediumint(8) unsigned NOT NULL default '0',
  source mediumint(8) unsigned default NULL,
  PRIMARY KEY  (track_id),
  KEY belso_cikksz (internal_item)
) TYPE=MyISAM;

INSERT INTO tracking VALUES("10000001", "0", "2", "10000087", "9224", "10.00", "10000007", NULL);
INSERT INTO tracking VALUES("10000002", "0", "2", "10000070", "9227", "10.00", "10000008", NULL);
[3 Jul 2003 15:34] Michael Widenius
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

In MySQL 4.0.13 we fixed a bug in SUM() handling, which indirectly caused this problem.

According to ANSI 99, SUM(#) on a group where all # values in the group is NULL should return NULL, which it now does in MySQL 4.0.13

In previous versions of MySQL we returned 0, but this was wrong and we had to fix it.  It's also documented in the Changelog manual section for 4.0.13

The fix for the current case is to use IFNULL(SUM(#),0) instead of SUM(#)