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);