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

Posted on March 3, 2011, in SQL Server, T-SQL and tagged , . Bookmark the permalink. 2 Comments.

  1. Agree that it’s definitely an edge case. I’ve also used CASE before for it’s short-circuiting properties, and I don’t intend to go back to try to update all of that code in light of this example. 🙂

  2. Filed on Connect, fixed already. Great people at MS. http://bit.ly/eROtCY

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: