SUM aggregation with decimal fails with a numeric overflow
SAP HANA does not behave the same as other RDBMS, which automatically upconvert the data type when the intermediate results cause an overflow.
The defined behavior is for SAP HANA not to up-convert during aggregation for performance reasons.
To prevent an overflow from occuring there are two options:
1. Use an explicit cast for query execution.
2. Use a datatype with higher precision.
Not all datatypes of DECIMAL(n,s) for n and s within ranges have an exact internal low level datatype representation.
As a result the SAP HANA Database guarantees to deliver results within the defined precision. If it can deliver a result of higher precision without overflow it will do so. In general only the aggregation within the defined precision is guaranteed.
This means there is only a guarantee that the datatypes work within their defined range.
The user should stay within the defined ranges to make sure the behaviour stays stable. Therefore only use a datatypes up to its maximum precision.
An aggregation like SUM is guaranteed to work within its precision ranges. This affects both final results and intermediate results. When in doubt, choose the larger data type. If a data type currently works with higher precision than defined then this is not a guaranteed behavior but due to internal optimizations.