Re: [HACKERS] sum(population) under Sybase

> Here is Sybase. Note, I declared the population field as int.
> 1> select sum(population) from t1
> 2> go
> Arithmetic overflow occurred.
>
> But with population defined as numeric(20,0)
> 1> select * from t2
> 2> go
> name population
> -------------------- -----------------------
> JAPAN 129947000
> CANADA 25610000
> U.S.A. 242080000
> MEXICO 81160000
> BRAZIL 141450000
> ARGENTINA 31500000
> INDIA 766140000
> CHINA 1072220000
> RUSSIA 281170000
>
> (9 rows affected)
> 1> select sum(population) from t2
> 2> go
>
> -----------------------------------------
> 2771277000
>
> I sort of like this behavior. This way, a valid sum over fields of
> type X can always be stored in a field of type X.

How is that? numeric(20,0) guarantees that at least 20 digits can be
stored. However, the SQL92 standard allows but does not require that
more than 20 digits are handled. So the standard does not preclude
overflow problems. In fact, many implementations will allocate a fixed
amount of storage for the numeric field, so would have trouble coping
with summation overflows.

- Tom