Re: [luciddb-users] Strange behaviour on SUM and SUM0

[Nicholas G. <ngo...@dy...>]

On Jul 3, 2012, at 1:55 AM, Matthias Dieter Wallnöfer wrote:

> Other database systems (eg. Postgres, 
> http://www.postgresql.org/docs/8.2/static/functions-aggregate.html) 
> perform implicit conversions to BIGINT, DOUBLE, NUMERIC before getting 
> started.

Matthias,

Thanks for emailing.

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.  

https://github.com/dynamobi/luciddb/issues/480
https://github.com/dynamobi/luciddb/issues/300
https://github.com/dynamobi/luciddb/issues/483

Nick