Click or drag to resize

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.

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.

Categories

Design Rules, New Rules

Additional Information
Example Test Script
 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

 MessageLineColumn
1SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates.149
2SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates.2510
3SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates.268
4SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates.2736
5SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates.2838
6SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates.4211
See Also

Other Resources