I had a nasty bug today in SQL with INT overflow:
SELECT SUM(COL) FROM MyTable
When the sum gets bigger than 2,147,483,647 in sql runtime it overflows.
"Arithmetic overflow error converting expression to data type int."
The bad thing is that I knew it. The SUM was also inside more complicated expression with assign to BIGINT parameter :(
BTW, SUM in T-SQL returns only INT, DECIMAL, MONEY, FLOAT according to BOL. So the fix should be like so:
SELECT SUM(CAST(COL AS DECIMAL)) FROM MyTable
Which looks wierd at a 1st glance, but is correct. Another point: SUM can also return BIGINT in MSSQL 2005 like so:
SELECT SUM(CAST(COL AS BIGINT)) FROM MyTable
But it is not documented...
Thursday, December 07, 2006 11:21 PM