Strange behaviour on SUM and SUM0

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Strange behaviour on SUM and SUM0

Matthias Dieter Wallnöfer
Dear Farrago/LucidDB devs,

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,
http://www.postgresql.org/docs/8.2/static/functions-aggregate.html)
perform implicit conversions to BIGINT, DOUBLE, NUMERIC before getting
started.

Thanks,
Matthias Wallnöfer


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|

Re: Strange behaviour on SUM and SUM0

ngoodman

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
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users