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