Blog Archives

A short-circuiting edge case


Bart Duncan (blog) found a very strange edge case for short-circuiting and commented on my article on sqlservercentral.

In my opinion it should be considered a bug. BOL says it clearly:

Searched CASE expression:

  • Evaluates, in the order specified, Boolean_expression for each WHEN clause.
  • Returns result_expression of the first Boolean_expression that evaluates to TRUE.
  • If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

What makes Bart’s example weird, is the fact that the ITVF seems to be the only scenario where the ELSE branch of the expression gets evaluated:

-- Autonomous T-SQL batch: everything runs just fine
DECLARE @input int
SELECT @input = 0
SELECT calculated_value =
    CASE
        WHEN @input <= 0 THEN 0
        ELSE LOG10 (@input)
    END
GO

-- Scalar function: runs fine
CREATE FUNCTION dbo.test_case_short_circuit2 (@input INT)
RETURNS int
AS BEGIN
RETURN (
    SELECT calculated_value =
        CASE
            WHEN @input <= 0 THEN 0
            ELSE LOG10 (@input)
        END
)
END
GO

SELECT dbo.test_case_short_circuit2 (-1);
GO

However, short-circuiting should never be something to rely upon: whenever there’s an alternative way to express the statement, I suggest using it.

2011/03/04 UPDATE:

Paul White (blog | twitter) agrees to consider this as a bug:

It is constant-folding at work. If you replace the literal constant zero with a variable, the problem no longer occurs. SQL Server expands the in-line TVF at optimization time and fully evaluates the CASE with the constant values available.
Constant-folding should never cause an error condition (such as an overflow) at compilation time – there have been other bugs in this area fixed for the same reason.

More details on the discussion thread of my article.

Advertisements