What happens if SQL sum() reach type capacity (overflow)?

Question

I understand that question is vendor dependent but ask should I worry if aggregate function like SUM operate on small type?

For example MariaDB uses 4 bytes for type INT. Developers may assume that each transaction have amount no bigger then a few thousands.

But what happen if we try to get income for a whole year for all departments? E.g.:

-- CREATE TABLE income (dt DATETIME, department INT, amount INT);
SELECT SUM(amount) FROM income WHERE dt BETWEEN '2014-01-01' and '2014-12-31'

It looks dumb to increasing storage size only to fix overflow issue with aggregate function SUM.

What I should worry about? Are there any guarantee or clarification from SQL 92/99/2008 standards?

Are there any special support from JDBC drivers?

Should I rewrite select in form:

SELECT SUM(CAST(amount AS BIGINT)) FROM income
  WHERE dt BETWEEN '2014-01-01' and '2014-12-31'

Answer 1

It's rather easy to test on mysql:

32bit overflow:

mysql> select sum(x) from (
    select pow(2,31) as x
    union all
    select pow(2,31)
    union all
    select pow(2,31)
) as bignums;
+------------+
| sum(x)     |
+------------+
| 6442450944 | // returned as a "bigint"
+------------+
1 row in set (0.00 sec)

64bit:

mysql> select sum(x) from (
    select pow(2,63) as x
    union all
    select pow(2,63)
    union all
    select pow(2,63)
) as bignums;
+-----------------------+
| sum(x)                |
+-----------------------+
| 2.7670116110564327e19 | // returned as float
+-----------------------+
1 row in set (0.00 sec)

Double:

mysql> select sum(x) from (
    select 1.7e+308 as x
    union all
    select 1.7e+308
    union all
    select 1.7e+308
) as bignums;
+--------+
| sum(x) |
+--------+
|      0 |
+--------+

It's rather easy to test on mysql:

32bit overflow:

mysql> select sum(x) from (
    select pow(2,31) as x
    union all
    select pow(2,31)
    union all
    select pow(2,31)
) as bignums;
+------------+
| sum(x)     |
+------------+
| 6442450944 | // returned as a "bigint"
+------------+
1 row in set (0.00 sec)

64bit:

mysql> select sum(x) from (
    select pow(2,63) as x
    union all
    select pow(2,63)
    union all
    select pow(2,63)
) as bignums;
+-----------------------+
| sum(x)                |
+-----------------------+
| 2.7670116110564327e19 | // returned as float
+-----------------------+
1 row in set (0.00 sec)

Double:

mysql> select sum(x) from (
    select 1.7e+308 as x
    union all
    select 1.7e+308
    union all
    select 1.7e+308
) as bignums;
+--------+
| sum(x) |
+--------+
|      0 |
+--------+

---

comment followup:

mysql> describe overflow
    -> ;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x     | int(11)    | YES  |     | NULL    |       |
| y     | bigint(20) | YES  |     | NULL    |       |
| z     | double     | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from overflow;
+------------+---------------------+---------+
| x          | y                   | z       |
+------------+---------------------+---------+
| 2147483647 | 9223372036854775807 | 1.7e308 |
| 2147483647 | 9223372036854775807 | 1.7e308 |
| 2147483647 | 9223372036854775807 | 1.7e308 |
+------------+---------------------+---------+
3 rows in set (0.00 sec)

mysql> select sum(x), sum(y), sum(z) from overflow;
+------------+----------------------+--------+
| sum(x)     | sum(y)               | sum(z) |
+------------+----------------------+--------+
| 6442450941 | 27670116110564327421 |      0 |
+------------+----------------------+--------+
1 row in set (0.00 sec)

Answer 2

Postgres handles this without overflow or truncation:

From the manual:

sum(expression), Return Type: bigint for smallint or int arguments, numeric for bigint arguments, otherwise the same as the argument data type

http://www.postgresql.org/docs/current/static/functions-aggregate.html

And a quick test proves that:

psql (9.4.5)
Type "help" for help.

postgres=> create table x (amount int);
CREATE TABLE
postgres=>
postgres=> insert into x values (2147483647), (2147483647);
INSERT 0 2
postgres=> select sum(amount)
wbtest-> from x;
    sum
------------
 4294967294
(1 row)

postgres=>

---

Interesting enough the SQL standard requires the statement to fail in this situation:

If, during the computation of the result of AF, an intermediate result is not representable in the declared type of the site that contains that intermediate result, then
...
Otherwise, an exception condition is raised: data exception — numeric value out of range.

^{(AF = aggregate function)}

Answer 3

When I understand you right, you are asking what happens in case of an overflow.

At least for SQL Server, look up this documentation:

https://msdn.microsoft.com/de-de/library/ms187810%28v=sql.120%29.aspx

Here it says what the return type of sum() is for specific input types:

Expression result               Return type
------------------------------------------------
tinyint                         int
smallint                        int
int                             int
bigint                          bigint
decimal category (p, s)         decimal(38, s)
money and smallmoney category   money
float and real category         float 

That means, there can indeed be an overflow. So I recommend you to use the type float or money for salaries, instead of the type int.