Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


35
0
Sign in
to vote
Type: Bug
ID: 546437
Opened: 3/31/2010 12:56:37 PM
Access Restriction: Public
1
Workaround(s)
view
10
User(s) can reproduce this bug

Description

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.
Details
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:

CASE WHEN V1
IS NOT NULL THEN
V1 ELSE COALESCE
(V2, ...,
Vn) END

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

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

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
from
(
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.

Sign in to post a workaround.
Posted by Paul White NZ on 4/1/2010 at 2:02 AM
DECLARE @Good int, @Bad int;
SET @Good = 0;
SET @Bad = 0;
WHILE @Good + @Bad < 1000
BEGIN;
IF COALESCE((SELECT TOP (1) (Nullable)
FROM Demo
WHERE SomeCol = 1), 1) IS NULL
BEGIN;
SET @Bad = @Bad + 1;
END;
ELSE
BEGIN;
SET @Good = @Good + 1;
END;
END;
SELECT @Good AS Good, @Bad AS Bad;