Why does COALESCE return multiple Invalid Column Name errors for a single column?

Question

When writing a SQL query using COALESCE, I've noticed that it will, under specific circumstances, throw multiple errors for a single invalid column name.

If I write the following query and try to execute it, I receive a single error message telling me, in short, that I'm an idiot and the column doesn't exist.

DECLARE @a TABLE (Column1 INT, Column2 INT)

-- This will return one error, complaining about NeverHeardOfIt
SELECT 
    COALESCE
      (
        Column1, 
        Column2, 
        NeverHeardOfIt
      ) 
FROM @a

Msg 207, Level 16, State 1, Line 8 Invalid column name 'NeverHeardOfIt'.

However, if I alter the query just slightly to place the invalid column earlier in the COALESCE statement, I will receive two errors:

DECLARE @a TABLE (Column1 INT, Column2 INT)

-- This will return two errors, both complaining about NeverHeardOfIt
SELECT 
    COALESCE
      (
        Column1, 
        NeverHeardOfIt, 
        Column2
      ) 
FROM @a 

Msg 207, Level 16, State 1, Line 7 Invalid column name 'NeverHeardOfIt'.

Msg 207, Level 16, State 1, Line 7 Invalid column name 'NeverHeardOfIt'.

I've tested a few different scenarios, and it seems that it only occurs when I am coalescing three or more columns, and the invalid column is not the last column. Obviously, "fixing" this is easy - don't reference invalid columns! But I'm curious to know why, exactly, the COALESCE() function throws the same error twice. The best I can come up with is that, under the hood, SQL is writing multiple statements, saying something along the lines of:

If Column 1 is NULL, use NeverHeardOfIt

If NeverHeardOfIt IS NULL, use Column2

If Column2 is NULL ...

If that's the case, then I when I execute it I could see how/why the function would return an error for each of the 'statements' that it generated behind the scenes.

Does anyone know if that is correct, or know of another reason this would return two errors?

Answer 1

I would consider the double error message a bug, but a very small one. But, the reason is that coalesce() (and choose() as well) is really shorthand. So:

coalesce(a, b, c)

Is really:

(case when a is not null then a
      when b is not null then b
      else c
 end)

Notice that the last element only appears once -- so you only get one error when the missing column is at the end.

This may seem like an arcane detail, but it is actually important. For instance, if one of the values is a subquery -- and subqueries can be expensive to run -- then it is run twice in the evaluation of the coalesce(). When performance is an issue and one or more the values are subqueries, then even I prefer isnull() which performs better in these cases. Normally, my bias is toward using ANSI standard functions.