It appears that the algebrizer transforms coalesce(a, b) to
CASE WHEN a IS NOT NULL THEN a ELSE b END
before optimizer gets a chance to see it. This is testified by the fact a
query like "SELECT coalesce(xyz, 0) FROM sys.objects" yields two error
messages for the missing column and not one.
When a is a subquery, this becomes
CASE WHEN (subquery) IS NOT NULL THEN (subquery) ELSE b END
This is unfortunate, because the optimizer does not seem to be able to match
the two instances of subquery, why it gets evaluated twice. This can easily be
worked around by using isnull instead, but coalesce is in the ANSI standard,
and coalesce is also more useful than isnull, since you can have more than
two parameters. And more importantly, few users would even suspect that
coalesce is bad.
In the repro, I use a SELECT query, because it was easier to compose such
a query. In real life, maybe the most important context is UPDATE statements
where you which to adhere to the ANSI standard. In this case, there are few choices