A NULLable Column Is Like A Gun

Question: What is the difference between a loaded gun and an empty gun?

Answer: There is no difference:

---

All guns are always loaded.

---

That's Rule Number One of Gun Safety, and a similar rule applies to NULLable database columns:

---

All NULLable columns contain NULLs.

---

It's actually worse with NULLable columns, there's such thing as a cable lock
to protect the unwary....  

What about COALESCE()?

In spite of its goofy name COALESCE() is a wonderful tool to protect you from NULLs. Here's an example:

Let's say your code is supposed to handle an INTEGER that could be NULL; it could be a column, a parameter, even a field in a file.

Let's also say that your code is supposed to treat a NULL value as if it was zero; here's a test:

BEGIN
   DECLARE x INTEGER;
   SET x = NULL;
   SELECT COALESCE ( x, 0 ) AS new_x;
   SET x = 5;
   SELECT COALESCE ( x, 0 ) AS new_x;
END; 

new_x
0

new_x
5

So far so good: you can call COALESCE() and it will turn NULL into zero while letting non-NULL values pass through unchanged.

Now let's say you discover that the input is also supposed to non-negative, and if it is negative you also want to change it to zero; here's a test:

BEGIN
   DECLARE x INTEGER;
   SET x = -5;
   SELECT IF x < 0
             THEN 0
             ELSE COALESCE ( x, 0 )
          END IF AS new_x;
   SET x = 5;
   SELECT IF x < 0
             THEN 0
             ELSE COALESCE ( x, 0 )
          END IF AS new_x;
END; 

new_x
0

new_x
5

So far so good, it changes -5 to zero and leaves 5 alone... but what if x is NULL? Let's see...

...but first, what do YOU think it will do?

What do you think the result set will be?

BEGIN
   DECLARE x INTEGER;
   SET x = NULL;
   SELECT IF x < 0
             THEN 0
             ELSE COALESCE ( x, 0 )
          END IF AS new_x;
END; 

Will new_x be 0?

Or will it be...

BEGIN
   DECLARE x INTEGER;
   SET x = NULL;
   SELECT IF x < 0
             THEN 0
             ELSE COALESCE ( x, 0 )
          END IF AS new_x;
END; 

new_x
(Null)

Gotcha!

NULLs are slippery, you cannot trust them!

And in this case, you cannot even LOOK at a NULL without wrapping it with COALESCE():

BEGIN
   DECLARE x INTEGER;
   SET x = NULL;
   SELECT IF COALESCE ( x, 0 ) < 0
             THEN 0
             ELSE COALESCE ( x, 0 )
          END IF AS new_x;
END; 

new_x
0

No, it's not a bug

It's a feature, part of the way NULL works, part of the way three-valued logic works: when x is NULL, IF x < 0 is neither TRUE nor FALSE, it is UNKNOWN, and unlike the IF statement, the IF expression doesn't take the ELSE path, it just turns UNKNOWN into NULL.

That's as dumb as a sack of rocks, right?

Well, yeah, sure... about as dumb as saying "this gun is empty, I can wave it around, pull the trigger..."

It is part of the nature of a NULLable column to break your code, just like it is part of the nature of a gun to blow your foot off.

---