Hugo Kornelis (3/31/2010)
...I submitted a bug report... a case I constructed, based on this discussion, where COALESCE(..., 1) may erroneously return NULL (which should of course never happen).
Voted, repro'd, and a sort-of 'workaround' added.
The optimizer seems to break its own rules here - using the two expressions as if they were interchangeable in the final Compute Scalar, and in the Pass Through expression on the second left join too.
Correct behaviour can be restored by ensuring column references are used instead of expressions:
IF COALESCE((SELECT TOP (1) Nullable
FROM Demo
WHERE SomeCol = 1), 1) IS NULL
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi