COALESCE - guaranteed to short-circuit?
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.
DECLARE @test INTSET @test = 1PRINT '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 intASBEGIN -- 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 / 0ENDgoselect dbo.evil()-- takes foreverselect ISNULL(1, dbo.evil())-- very fastselect COALESCE(1, dbo.evil())-- very fast
Here is some proof that the underlying implementation with CASE will execute sub queries.
DECLARE @test INTSET @test = 1select 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.
The efficient way to guarantee short circuit in MS SQL Server is to use CASE.For the success WHEN clause, no others are evaluated.
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 TestCalcFROM Fractions
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 TestCalcFROM #Fractions
The result I got was:
Numerator Denominator TestCalc1 1 11 2 0.51 3 0.33333333333333351 0 02 0 03 0 00 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!)?