case when null evaluates to false

Question

Today I was surprised by this `case` behaviour: ``` select case when null then true else false end; case ------ f ``` I would expect it to return `null` since a `null` casted to boolean yelds a `null` not a `false`: ``` select null::boolean is null; ?column? ---------- t ``` Any comments on the rationale of this behaviour? What Am I missing?

Accepted Answer

You're thinking of the `CASE` expression like it was taking the `null` as input to a function or operator, where null input generally results in null output:

```
regress=> SELECT 't'::boolean = NULL::boolean;
 bool 
------

(1 row)
```

wheras in fact it behaves like a `WHERE` clause in terms of null handling:

```
craig=> SELECT 't' WHERE NULL;
 ?column? 
----------
(0 rows)
```

In `WHERE` clauses - and in `CASE`, a `NULL` result from a test expression is treated as "not true and therefore false". In some ways it's regrettable that the SQL standard didn't make a `NULL` result in a `WHERE` expression an error instead of treating it as false, but that's how it is.

This is yet another painful symptom of `NULL`'s split personality, where the SQL spec can't decide if `NULL` means "unknown/undefined value" or "the absence of a value", much like the horrible mess with `NULL`s and aggregates.

case when null evaluates to false

Solution

Problem