MySQL Bugs: #4530: a query using sum() reports wrong total

Bug #4530	a query using sum() reports wrong total
Submitted:	13 Jul 2004 1:17	Modified:	13 Aug 2004 11:08
Reporter:	Derek Miller	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.20 and 4.0.18	OS:	Linux (linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
i have a large query that is returning the wrong sum() value here is the origional query:

SELECT s.shipment_id, s.date_created, s.country, s.container_size, sum(b.weight),
count(distinct c.box_num), s.spon_type, s.ship_spon,  
sum(c.quantity * i.repl_value), sum(c.quantity * i.dona_value)
FROM shipment as s, box as b, inventory as i, box_items as c
Where s.shipment_id = b.shipment_id  AND c.item_id = i.item_id AND b.shipment_id = c.shipment_id AND s.shipment_id = c.shipment_id
GROUP BY b.shipment_id, s.shipment_id
order by s.shipment_id

shipment_id 5 has 5 records in b.weight of 5 lbs and it returns 225  i dont know how it comes to that , maybe it takes the 25 and multiplies it by 7 (which is the amount of rows in box_items that correspond to shipment_id of 5) only the sum(b.weight) gets screwed up, it doesnt mess up the sum(c.quantity * i.repl_value), sum(c.quantity * i.dona_value) 

so i eventually cut it down to this query and found that including the inventory table messes it  up 

SELECT s.shipment_id, sum(b.weight)
FROM shipment as s, box as b, inventory as i
Where s.shipment_id = b.shipment_id AND s.shipment_id = 5
group by s.shipment_id

that query returns the incorrect sum of b.weight multiplied by the number of records in the inventory table for each shipment_id. the correct sum would be 5 records of 5 so sum() should return 25 but instead it returns 22975 witch is 25 times 919, the amount of records in the inventory table, it i add an item to the inventory table it reflects hte new multiplier

if i omit the inventory table it comes out fine 

How to repeat:
i have tried it on 2 different systems running gentoo linux 2.6.7 with mysql 4.0.18 and 4.0.20 and exact same problem on each. i have even reinstalled mysql to no avail. also check table returns no problems. if you would like a dump of my tables so far let me know ( right now its not very big at all)

Suggested fix:
i have no idea

> SELECT s.shipment_id, sum(b.weight)
> FROM shipment as s, box as b, inventory as i
> Where s.shipment_id = b.shipment_id AND s.shipment_id = 5
> group by s.shipment_id

You're missing a join to the inventory table here
so it is expected behavior that you get the cartesian
product of all shipments by all inventory entries.

You're original query looks fine though, but it is hard to 
tell what is going wrong without seeing the data it operates on ...

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".