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:
None 
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
Description:
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

CREATE TABLE tempId (
  id int(11) NOT NULL auto_increment,
  PRIMARY KEY  (id)
) ENGINE=MyISAM;

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)

result1  
---------------------------
2161727821137839360 

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)

result2 
---------------------------
2161727821137839104 

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 
2161727821137839360
[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