CASE expression evaluates ELSE branch at compile time - by Gianluca Sartori

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 649957 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 3/7/2011 9:15:56 AM
Access Restriction Public


Originally described here by Bart Duncan:
The statements in the "steps to reproduce" section create an ITVF and try to select a value out of it, passing zero as argument. 
The SELECT statement fails at compile time, due to the evaluation of the ELSE branch of the CASE expression.

Books Online seems to guarantee that short-circuiting applies to CASE expressions:

Quoting :

"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."

Reproduced also on SQL Server 2005 SP3 and SQL Server 2008 SP2.
Sign in to post a comment.
Posted by Newfangled Old-fashioned Stuff on 9/15/2014 at 10:35 AM
For those who are interested, I tested as follows:
9.00.5324 - broken
10.0.5869 - broken
10.50.4321 - broken
11.0.5522 - fixed
12.0.2381 - fixed
Posted by AaronBertrand on 2/5/2013 at 1:03 PM
In what build(s) was this fix first introduced? It's been closed as fixed, but where?
Posted by TechVsLife2 on 2/12/2012 at 4:52 PM
Note: will not get fixed: aggregates do not follow case order.
Posted by Paul White NZ on 7/19/2011 at 1:05 AM
Fixed in SQL Server code name Denali CTP 3 (build 11.0.1440.19)
Not fixed in SQL Server 2008 R2 SP1 CU1 (build 10.50.2769.0)

Fixed estimated plan shows a Constant Scan containing the following expression definition:
(Scalar Operator(CASE WHEN (1) THEN (0.000000000000000e+000) ELSE log10((0.000000000000000e+000)) END))
Posted by Microsoft on 3/11/2011 at 5:52 PM
Thanks for reporting the issue. We will investigate it and let you know what we find.

Umachandar, SQL Programmability Team
Posted by Paul White NZ on 3/9/2011 at 8:19 AM
It seems this bug occurs whenever an invalid floating point operation results from a constant-folded expression.
The error occurs with LOG, LOG10, ACOS, and ASIN whever an illegal value for the function is supplied.
The error does not occur with other classes of exception e.g. attempting to divide by zero (thanks Adam Machanic for that one).