A short-circuiting edge case
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.
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.