On 18-05-2006 15:35:53 +0200, S.Idreos@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.