Bug #35287 SUM( a - b - c ) different to SUM( a ) - SUM( b ) - SUM( c )
Submitted: 14 Mar 2008 10:51 Modified: 31 Mar 2008 20:26
Reporter: kinfei lee Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:4 and 5 OS:Windows
Assigned to: CPU Architecture:Any

[14 Mar 2008 10:51] kinfei lee 
Description:
device_id | SUM( a.quantity ) | SUM( b.quantity ) | SUM( ship.lotquantity ) | SUM( a.quantity - ship.lotquantity - b.quantity ) | SUM( a.quantity ) - SUM( ship.lotquantity ) - SUM( b.quantity )

ACFM-7101-A | 2233539 | 179474 | 2052340 | 1265 | 1725

The SUM( a.quantity - ship.lotquantity - b.quantity ) = 1265
SUM( a.quantity ) - SUM( ship.lotquantity ) - SUM( b.quantity ) = 1725

How to repeat:
My table having more then 100k rows of records
[14 Mar 2008 11:27] Valeriy Kravchuk 
Thank you for a problem report. Please, send/upload a complete test case, with data, or SHOW CREATE TABLE results at least.
[17 Mar 2008 5:53] kinfei lee 
Sorry, that's my mistakes. that the c or b having null values so it will caused the problem. So i should make the query like this for correct. SUM( IFNULL( a, 0 ) - IFNULL( b, 0 ) - IFNULL( c, 0 ) ) = SUM( a ) - SUM( b ) - SUM( c ).

the problem with a - b - c.. if b is null then if a - null - c = null.
[17 Mar 2008 11:34] MySQL Verification Team 
Thank you for the feedback. If I understood isn't a bug otherwise could you please
provide the complete script test case as requested by Valeriy. Thanks in advance.
[31 Mar 2008 4:02] kinfei lee 
Here are sample testing

(SELECT 2 AS A, 3 AS B, 4 AS C)
UNION (SELECT NULL , 5, 6) 

A-------B-------C
2-------3-------4
NULL----5-------6

SELECT SUM( A ) , SUM( B ) , SUM( C ) , SUM( A - B - C ) , SUM( A ) - SUM( B ) - SUM( C ) , SUM( IFNULL( A, 0 ) - IFNULL( B, 0 ) - IFNULL( C, 0 ) )
FROM (
(SELECT 2 AS A, 3 AS B, 4 AS C)
UNION (SELECT NULL , 5, 6)
)d 

SUM( A )---------------------------------------------------  2
SUM( B )---------------------------------------------------  8 
SUM( C )---------------------------------------------------  10 
SUM( A - B - C)--------------------------------------------  -5 
SUM( A ) - SUM( B ) - SUM( C )-----------------------------  -16
SUM( IFNULL( A, 0 ) - IFNULL( B, 0 )- IFNULL( C, 0 ) )-----  -16
[31 Mar 2008 20:26] Sveta Smirnova 
Thank you for the feedback.

There is NULL in column A.

So:

SUM( A - B - C) = (2-3-4) + NULL = -5
SUM( A ) - SUM( B ) - SUM( C ) = (2) - (3+5) - (4+6) = -16
SUM( IFNULL( A, 0 ) - IFNULL( B, 0 )- IFNULL( C, 0 ) ) = (2) - (3+5) - (4+6) = -16

So this is expected behavior.

See also http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html