[Monetdb-developers] semantics of aggregation operations

Fabian Groffen Fabian.Groffen at cwi.nl
Thu May 18 15:56:06 CEST 2006

On 18-05-2006 15:35:53 +0200, S.Idreos at cwi.nl wrote:
> I just noticed for example that the sum operation will return nil if one
> or more of the BUNs in the BAT is nil.
> That is probably not the semantics that someone would expect, i.e., I
> would like to see the actual result by ignoring the nils.

In SQL this is the case.  Doesn't mean it's logical ;)  According to SQL
it's "most useful in practise".

> Given the current semantics the code that SQL generates has to first
> remove nils (with a select operation) and then apply the sum operation on
> a BAT. That costs more time and also results in one more intermediate
> result.
> What about updating the sum implementation to ignore nils? How much would
> affect that the various projects on top of MonetDB?

Maybe a sum_no_nil function or something next to sum to avoid side
issues in other projects?

A small detail of SQL spec that might be relevant:
If a column contains only NULL or no values at all the result of the
aggregate function is NULL.  COUNT is an exception, obviously.

More information about the developers-list mailing list