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.
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.
Posted on March 3, 2011, in SQL Server, T-SQL and tagged Short-Circuit, T-SQL. Bookmark the permalink. 2 Comments.
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. 🙂
Filed on Connect, fixed already. Great people at MS. http://bit.ly/eROtCY