SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates

The topic describes the SA0174 analysis rule.

Message

The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates

Description

The rule checks usage of aggregate functions or full text search predicates inside CASE expression.

The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied.

In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible.

Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement.

For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.

WITH Data (value) AS

(

SELECT 0

UNION ALL

SELECT 1

)

SELECT

CASE

WHEN MIN(value) <= 0 THEN 0

WHEN MAX(1/value) >= 100 THEN 1

END

FROM Data ;

You should only depend on order of evaluation of the WHEN conditions for scalar expressions (including non-correlated sub-queries that return scalars), not for aggregate expressions.

How to fix

Review the CASE expression and suppress the rule after considering the unreliable short-circuit behavior of aggregate functions and full text search predicates.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
20 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1declare @v int = 0
 2SELECT 
 3  CASE 
 4    WHEN @v = 0 
 5      THEN 1 
 6    ELSE (SELECT MIN(1/@v))
 7  END; 
 8
 9SELECT 
10  CASE 
11    WHEN @v = 0 
12      THEN 1 
13    ELSE MAX(1/@v) 
14  END; 
15
16DECLARE @SearchWord nvarchar(30) = ''
17SELECT Description   
18FROM Production.ProductDescription   
19WHERE  
20    case
21    when @SearchWord  = '' or @SearchWord is null then 1 
22        when  1= (select top (1) 1 from Production.ProductDescription where FREETEXT(Description, @SearchWord) ) then 1
23        when  1= (select top (1) 1 from Production.ProductDescription where contains(Description, @SearchWord) ) then 1
24    when  FREETEXT(Description, @SearchWord) then 1
25         when  contains(Description, @SearchWord) then 1
26  when (@SearchWord is not null and contains(Description,@SearchWord)) then 1
27    when (@SearchWord is not null and freetext(Description,@SearchWord)) then 1
28
29    else 0 end = 1
30
31
32;WITH Data (value) AS   
33(   
34SELECT 0   
35UNION ALL   
36SELECT 1   
37)   
38SELECT   
39   CASE   
40      WHEN MIN(value) <= 0 THEN 0   
41      WHEN MAX(1/value) >= 100 THEN 1   
42   END
Analysis Results
  Message Line Column
1 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 14 9
2 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 25 10
3 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 26 8
4 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 27 36
5 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 28 38
6 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 42 11
See Also

Other Resources

Analysis Rules