Bug #16205 SUM() function is not handling correctly big integers
Submitted: 4 Jan 2006 22:10 Modified: 8 Jan 2006 15:19
Reporter: Sergey Rustamov Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.14-standard-log OS:sentos 4.1
Assigned to: CPU Architecture:Any

[4 Jan 2006 22:10] Sergey Rustamov
sum() function ignores some of the entries in the table when calculation involves big integers.

How to repeat:
I have table which looks like this

  id int(11) NOT NULL auto_increment,

and that table has 60 rows where id set from 1 to 60.

If I run this query

select  CAST( SUM(pow(2,id)) as UNSIGNED) as result1
from tempId
where id in (1,2,3,4,5,6,7,10,57,58,59,60)


the result integer does not include any pow() from 1 to 6.

if you run this

select  CAST( SUM(pow(2,id)) as UNSIGNED) as result2
from tempId
where id in (7,10,57,58,59,60)


the result is less then first query result on 256 but it should be less on 126 

Any combination of 1 to 6 will result in number equal to result1 
[8 Jan 2006 15:19] Hartmut Holzgraefe
a simple rounding error case

POW() returns a float value, SUM() summs up floats and looses some precision, 
only the SUM() result is casted to UNSIGNED ...

in MySQL 5.0 the following statement returns the right result:

 select  SUM(CAST((pow(2,id)) as UNSIGNED)) as result1
    from tempId where id in (1,2,3,4,5,6,7,10,57,58,59,60)\G
  *************************** 1. row *************************** 
  result1: 2161727821137839358

as here the result of POW() is already casted to UNSIGNED
before it is summed up using SUM()

in 4.1 this will still lead to a rounding error though, 
only 5.0 produces the exact result due to its better
high precision math features