COALESCE - guaranteed to short-circuit?

Question

From this question, a neat answer about using COALESCE to simplify complex logic trees. I considered the problem of short circuiting.

For instance, in functions in most languages, arguments are fully evaluated and are then passed into the function. In C:

int f(float x, float y) {
    return x;
}

f(a, a / b) ; // This will result in an error if b == 0

That does not appear to be a limitation of the COALESCE "function" in SQL Server:

CREATE TABLE Fractions (
    Numerator float
    ,Denominator float
)

INSERT INTO Fractions VALUES (1, 1)
INSERT INTO Fractions VALUES (1, 2)
INSERT INTO Fractions VALUES (1, 3)
INSERT INTO Fractions VALUES (1, 0)
INSERT INTO Fractions VALUES (2, 0)
INSERT INTO Fractions VALUES (3, 0)

SELECT Numerator
    ,Denominator
    ,COALESCE(
        CASE WHEN Denominator = 0 THEN 0 ELSE NULL END,
        CASE WHEN Numerator <> 0 THEN Numerator / Denominator ELSE NULL END,
        0
    ) AS TestCalc
FROM Fractions

DROP TABLE Fractions

If it were evaluating the second case when Denominator = 0, I would expect to see an error like:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

I found some mentions related to Oracle. And some tests with SQL Server. Looks like the short-circuiting might break down when you include user-defined functions.

So, is this behavior supposed to be guaranteed by the ANSI standard?

Southwestward

Highly Related – Darryldarryn

To summarize the DBA answer, SELECT COALESCE(1, (SELECT 1/0)) runs without error and shows that it short-circuits. The interpreter sees it as a shortened CASE statement. – Whitacre

Answer 1

I just had a look at the linked article and can confirm short circuiting can fail for both COALESCE and ISNULL.

It seems to fail if you have any sub-query involved, but it works fine for scalar functions and hard coded values.

For example,

DECLARE @test INT
SET @test = 1
PRINT 'test2'
SET @test = COALESCE(@test, (SELECT COUNT(*) FROM sysobjects))
SELECT 'test2', @test
-- OUCH, a scan through sysobjects

COALESCE is implemented according to the ANSI standard. It is simply a shorthand for a CASE statement. ISNULL is not part of the ANSI standard. Section 6.9 does not seem to require short circuiting explicitly, but it does imply that the first true clause in the when statement should be returned.

Here is some proof that is works for scalar based functions (I ran it on SQL Server 2005):

CREATE FUNCTION dbo.evil
(
)
RETURNS int
AS
BEGIN
    -- Create an huge delay
    declare @c int
    select @c = count(*) from sysobjects a
    join sysobjects b on 1=1
    join sysobjects c on 1=1
    join sysobjects d on 1=1
    join sysobjects e on 1=1
    join sysobjects f on 1=1
    return @c / 0
END
go

select dbo.evil()
-- takes forever

select ISNULL(1,  dbo.evil())
-- very fast

select COALESCE(1,  dbo.evil())
-- very fast

Here is some proof that the underlying implementation with CASE will execute sub queries.

DECLARE @test INT
SET @test = 1
select
    case
        when @test is not null then @test
        when @test = 2 then (SELECT COUNT(*) FROM sysobjects)
        when 1=0 then (SELECT COUNT(*) FROM sysobjects)
        else (SELECT COUNT(*) FROM sysobjects)
    end
-- OUCH, two table scans. If 1=0, it does not result in a table scan.

Milly

Yes, it looks like COALESCE is totally equivalent to CASE, and short-circuits the same way, however, as you show, the behavior of CASE does not always short circuit, which IS really quite nasty. – Southwestward

COALESCE does short-circuit correctly (even with subqueries) in 11g – Mayhem

It does not do 2 table scans even though the plan shows 2 scans. This is easy to verify with SET STATISTICS IO ON or simply look at the "number of executions" in the execution plan properties. There is an issue with COALESCE that does not occur with ISNULL though. – Darryldarryn

Answer 2

The efficient way to guarantee short circuit in MS SQL Server is to use CASE. For the success WHEN clause, no others are evaluated.

COALESCE can have issues

In this instance, why have so many branches in the COALESCE/CASE constructs?

SELECT Numerator
    ,Denominator
    ,CASE
        WHEN Denominator = 0 THEN 0 END,
        ELSE Numerator / Denominator
     END AS TestCalc
FROM Fractions

Leavetaking

See my answer, there is an underlying issue with CASE which flows up to ISNULL etc ... – Milly

Yes, CASE can do subqueries but I0m not sure of the relevance to the OP's question. I've seen it used as a short circuit but I don't like it personally because of the table scans or increase in IO (as you demonstrated) – Leavetaking

Answer 3

I was also surprised to see that answer works! I'm not sure this behaviour is guaranteed. (But I have not been able to find an example that does not work!)

Five years of SQL, and I'm still surprised.

I also went ahead and did one more change:

INSERT INTO #Fractions VALUES (0, 0)

SELECT Numerator
    ,Denominator
    ,coalesce (
        CASE WHEN Denominator = 0 THEN 0 ELSE NULL END,
        CASE WHEN Numerator <> 0 THEN Numerator / Denominator ELSE NULL END)
     AS TestCalc
FROM #Fractions

The result I got was:

Numerator   Denominator TestCalc
1             1           1
1             2           0.5
1             3           0.3333333333333335
1             0           0
2             0           0
3             0           0
0             0           0

Now I'm even more confused! For the case when num=0 and den=0, how did I get testcalc as 0 (especially since I removed the 0 after the last case!)?

Nawrocki

That should fall into the first case. Over a decade of SQL Server, and I never considered COALESCE would short-ciruict, because it LOOKS like a function call. Obviously CASE does, and it seams like COALESCE is defined to function identically to CASE. – Southwestward

my bad ... of course it falls into the first case. It's now my life's mission to find a case where this does not work :) – Nawrocki

@Learning, make sure you have a look at my expanded answer, it corrects some stuff. – Milly