Sunday, 4 September 2016

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

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'

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...