[Monetdb-developers] semantics of aggregation operations
Hi all, I would like to open a discussion on the semantics of operations. 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. 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? Regards, Stratos
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.
[...] 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.
Yes, I think it is relevant. Because the semantics of SUM are as is, a SQL query can tell an all-NULL column (yields NULL) from an empty aggregation input set (yields 0). I vote for the status quo. Cheers, --Teggy -- | Prof. Dr. Torsten Grust grust@in.tum.de | | http://www-db.in.tum.de/~grust/ | | Database Systems - Technische Universität München (Germany) |
On Thu, May 18, 2006 at 04:14:08PM +0200, Torsten Grust wrote:
[...] 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.
Yes, I think it is relevant. Because the semantics of SUM are as is, a SQL query can tell an all-NULL column (yields NULL) from an empty aggregation input set (yields 0). I vote for the status quo.
The current sum (or any of the aggregates except count) has the problem with NULLs. Allthough the SQL logics may not be perfect for everybody, just adding yet another set of aggregates with other semantics isn't good for maintainability. So I'd like you all to reconsider, ie clear reasons why the current semantics are better and/or required. Niels
Cheers, --Teggy -- | Prof. Dr. Torsten Grust grust@in.tum.de | | http://www-db.in.tum.de/~grust/ | | Database Systems - Technische Universität München (Germany) |
------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid0709&bid&3057&dat1642 _______________________________________________ Monetdb-developers mailing list Monetdb-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl
participants (4)
-
Fabian Groffen -
Niels Nes -
S.Idreos＠cwi.nl -
Torsten Grust