[prev in list] [next in list] [prev in thread] [next in thread] 

List:       monetdb-users
Subject:    [MonetDB-users] SUM of NULLs is 0?
From:       kelas <kelaskin () gmail ! com>
Date:       2009-02-28 15:01:51
Message-ID: 3468913d0902280701u28fd25eaoc2d93702c2b1b21a () mail ! gmail ! com
[Download RAW message or body]

Dear MonetDB-users,

a complete newbie to MonetDB, I can't really tell whether it is a bug,
feature or I'm just getting it all wrong.

A script below illustrates some weird NULL handling behaviour found in
Nov2008-SP2:


-- years under audit
CREATE TABLE BUDGET_PERIOD (BP smallint NOT NULL);
INSERT INTO BUDGET_PERIOD VALUES (1998);
INSERT INTO BUDGET_PERIOD VALUES (1999);
INSERT INTO BUDGET_PERIOD VALUES (2000);
INSERT INTO BUDGET_PERIOD VALUES (2001);
INSERT INTO BUDGET_PERIOD VALUES (2002);
INSERT INTO BUDGET_PERIOD VALUES (2003);
INSERT INTO BUDGET_PERIOD VALUES (2004);
INSERT INTO BUDGET_PERIOD VALUES (2005);

-- annual expenditure data
CREATE TABLE EXPENDITURE (BP smallint NOT NULL, AMOUNT DECIMAL(15,2));
INSERT INTO EXPENDITURE VALUES (1999, 0.00);
INSERT INTO EXPENDITURE VALUES (2000, 1.00);
INSERT INTO EXPENDITURE VALUES (2001, 2.00);
INSERT INTO EXPENDITURE VALUES (2002, 3.00);
INSERT INTO EXPENDITURE VALUES (2003, 4.00);
INSERT INTO EXPENDITURE VALUES (2004, NULL);

-- calculate past years expenditure as of year: expected to be NULL
for 1998, returns 0
SELECT
    A.BP,
    SUM( B.AMOUNT ) AS TOTAL_EXPD_ASOF
FROM BUDGET_PERIOD A LEFT JOIN EXPENDITURE B ON (A.BP >= B.BP)
GROUP BY A.BP
ORDER BY A.BP

-- same as above using CASE: returns all nulls (?!)
SELECT
    A.BP,
    SUM( CASE WHEN A.BP >= B.BP THEN B.AMOUNT ELSE NULL END )
FROM
BUDGET_PERIOD A, EXPENDITURE B
GROUP BY A.BP
go


Regards,
Vladimir

------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users
[prev in list] [next in list] [prev in thread] [next in thread]