COALESCE((subquery), 1) may return NULL - by Hugo Kornelis

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 546437 Comments
Status Closed Workarounds
Type Bug Repros 10
Opened 3/31/2010 12:56:37 PM
Access Restriction Public


The SQL Server engine chooses an execution plan for COALESCE((subquery), 1) where the subquery is executed twice. This is not only bad for performance, it can also cause incorrect results to be returned if the data referenced in the subquery is changed during execution.
Sign in to post a comment.
Posted by Paul White NZ on 1/16/2012 at 10:27 AM
Ha! Nice one UC - but all bets are off under READ UNCOMMITTED anyway. We're lucky we get results at all at that isolation level in SQL Server, consistency would be most unexpected ;c)
Posted by Microsoft on 1/9/2012 at 12:04 PM
Hello Hugo,
Thanks for your feedback. The behavior you are seeing is by design. COALESCE is not a function call - it is rewrite of CASE expression of form:

(V2, ...,

This is according to ANSI SQL standard. And when we rewrite COALESCE to CASE expression then the value expression (v1, v2, v3) will be copied multiple times. Also in the standard, a value expression containing a sub-query is considered non-deterministic. So during execution, depending on your isolation level you can get different results. If you want stable results then project the expression like Andrew suggested or use snapshot isolation and so on.
I should also note that the workaround that Paul posted will show different results if you run the query in READ UNCOMITTED mode. So you cannot rely on that too.

Umachandar, SQL Programmability Team
Posted by Ian Cowley on 3/25/2011 at 5:08 AM
There is an intermittent problem with coalesce (SQL version: 10.50.1600), when using varchar(max)

select coalesce(MyVarCharMaxColumn,'') from dbo.SomeTable

intermittently returns a null, when there is no subquery

workaround for the minute is

select isnull(MyVarCharMaxColumn,'') from dbo.SomeTable

Posted by Microsoft on 4/1/2010 at 10:06 AM
Thank you for filing this bug.

The problem is that an early part of our processing rewrites "COALESCE(expression1, expression2)" as "CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END". In your case (changing the if condition to a select), we generate

    when (SELECT Nullable FROM Demo WHERE SomeCol = 1) is not null
    then (SELECT Nullable FROM Demo WHERE SomeCol = 1)
    else 1

Later stages of query processing don't understand that the two subqueries were originally the same expression, so they execute the subquery twice. If the first execution sees a non-NULL value then we will take the result of the second execution even if it is NULL.

One workaround, though I hate to suggest it, is to change COALESCE to ISNULL, since the latter doesn't duplicate the subquery.

Another workaround, which is what the server should be doing automatically, is to push the subquery down into a subselect, like

select case when x is not null then x else 1 end
select (SELECT Nullable FROM Demo WHERE SomeCol = 1) as x
) as T;

Again, thanks for bringing this issue to our attention.

Andrew Richardson
Developer, SQL Server Query Optimizer.