On 09/01/2012 02:07 PM, Thalis Kalfigkopoulos wrote:
> Thanks all for the replies. Actually I had already tested that sum()
> behaved correctly with respect to NULLs, meaning that it ignored them
> (or treated them as 0, couldn't really tell). That's why I went ahead
> sum()ing even though I knew NULLs would always be involved.
> Unfortunately I didn't see what now seems obvious, that the comparison
> would be =NULL and not IS NULL.
The main thing to remember is that there are no consistent rules around
NULL. Learn each case and don't try to generalize too much.
Think:
1 + 2 + 3 = 6
1 + 2 + NULL = NULL
so obviously
sum(y) FROM ( VALUES (1),(2),(3) ) x(y) = 6
sum(y) FROM ( VALUES (1),(2),(NULL) ) x(y) = NULL
right? No, actually sum() over 1,2,NULL is 3, not NULL.
NULL isn't consistent.
--
Craig Ringer