Re: CASE/WHEN behavior with NULLS

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