How to deal with silent mysql sum() integer overflow?
mysql, overflow, sql, sum
Solution
You can double the range by casting the value to an unsigned value:
SELECT SUM(CAST(myIntColumn AS UNSIGNED)) ...
There is a bigger data type: the BIGINT, but unfortunately you cannot `CAST()` to it. If you want to make use of it, you must change your column to that type:
ALTER TABLE myTable CHANGE COLUMN myIntColumn myBigIntColumn BIGINT UNSIGNED ...
Problem
I've got this table with an `int(11)` column and hundreds of millions of rows. When I run a query like ``` SELECT SUM(myIntColumn) as foo FROM myTable; ``` the return value does not make sense--it is smaller than the the single largest max value. My values for this column max out somewhere around 500m, and the signed int should be able to handle ~2bil, so I assume mysql is experiencing an integer overflow, and keeping mum about it. What to do? Miscellaneous details that might just matter but probably not: - `mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (x86_64) using readline 5.2` - `mysqld Ver 5.0.75-0ubuntu10 for debian-linux-gnu on x86_64 ((Ubuntu))` - `Linux kona 2.6.28-11-server #42-Ubuntu SMP Fri Apr 17 02:45:36 UTC 2009 x86_64 GNU/Linux`