CODEWITHSUNDEEP
sqlmysqloverflowsum
Stu Thompson
Stu Thompson

Reputation: 38908

How to deal with silent mysql sum() integer overflow?

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:

Upvotes: 4

Views: 5158

Answers (1)

soulmerge
soulmerge

Reputation: 75774

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 ...

Upvotes: 6

Related Questions