I think that I have discovered a bug (or at least some strange
behaviour) in respect to the SUM and SUM0 aggregate functions. The
problem consists in the fact that these functions do always operate with
the datatype of the input (SQLSumAggFunction.java and
SQLSumEmptyIsZeroAggFunction.java). This is not an issue for big
integers or big floating point values but it turns out to be fatal when
summing up rather small values (TINYINT, SMALLINT, FLOAT) which likely
ends up in overflows.
Other database systems (eg. Postgres,
perform implicit conversions to BIGINT, DOUBLE, NUMERIC before getting
This is one example where, in my opinion, the ANSI standard got it wrong. The standard states (summarized) that the datatype of the aggregate output SUM(X) should be the same as original datatype (X). LucidDB and Farrago are compliant in this regard (ugggh). The issue and shortcoming of LucidDB/Farragos implementation is that the overflow should be detected and a warning should be issued to the caller to detect the overflow.
So... it's not really a bug according to the standard but it's certainly not very user friendly. :(
One possibility would be to add a plugin / configuration option that would rewrite queries to implicitly cast the inside X to larger datatypes.