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

Using aggregate functions or full-text search predicates within CASE expressions can lead to unexpected behavior and inefficient query execution.

Description

In T-SQL code and SQL Server, a common problem arises when aggregate functions or full-text search predicates are used within CASE expressions. The CASE statement processes conditions sequentially and may encounter issues if aggregates are prematurely evaluated before the CASE logic is applied.

For example:

SQL
Copy
 1WITH Data (value) AS   
 2(   
 3SELECT 0   
 4UNION ALL   
 5SELECT 1   
 6)   
 7SELECT   
 8   CASE   
 9      WHEN MIN(value) <= 0 THEN 0   
10      WHEN MAX(1/value) >= 100 THEN 1   
11   END   
12FROM Data;

This query can lead to a divide-by-zero error during evaluation of the MAX function, before the CASE expression fully executes.

  • Early evaluation of aggregate functions can trigger errors that disrupt intended query logic.

  • Such preemptive evaluations can lead to unintended exceptions, like divide-by-zero, due to input values not being processed as expected.

How to fix

Address issues caused by using aggregate functions or full-text search predicates inside CASE expressions to prevent unexpected errors.

Follow these steps to address the issue:

  1. Review the CASE expression to ensure that aggregate functions are not evaluated prematurely. Consider using alternative logic structures if necessary.

  2. Avoid using aggregate functions directly in CASE conditions. Instead, pre-compute necessary values in a Common Table Expression (CTE) or subquery.

  3. Simplify conditions to prevent errors such as divide-by-zero by isolating them from potential problematic inputs.

For example:

SQL
Copy
 1WITH PreComputedValues (minValue, maxValue, safeDivision) AS   
 2(   
 3    SELECT MIN(value), MAX(1.0 / NULLIF(value, 0)), 100 AS safeDivision   
 4    FROM Data   
 5)   
 6SELECT   
 7   CASE   
 8      WHEN minValue <= 0 THEN 0   
 9      WHEN maxValue >= safeDivision THEN 1   
10   END   
11FROM PreComputedValues;

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 AS INT = 0
 2
 3SELECT  CASE
 4            WHEN @v = 0 THEN 1
 5            ELSE( SELECT  MIN( 1 / @v ) )
 6        END;
 7
 8SELECT  CASE
 9            WHEN @v = 0 THEN 1
10            ELSE MAX( 1 / @v )
11        END;
12
13DECLARE  @SearchWord AS NVARCHAR( 30 ) = ''
14
15SELECT  Description
16FROM  Production.ProductDescription
17WHERE  CASE
18           WHEN @SearchWord = '' OR
19           @SearchWord IS NULL THEN 1
20           WHEN 1 = ( SELECT TOP( 1 )
21                              1
22                      FROM  Production.ProductDescription
23                      WHERE  FREETEXT( Description, @SearchWord ) ) THEN 1
24           WHEN 1 = ( SELECT TOP( 1 )
25                              1
26                      FROM  Production.ProductDescription
27                      WHERE  CONTAINS( Description, @SearchWord ) ) THEN 1
28           WHEN FREETEXT( Description, @SearchWord ) THEN 1
29           WHEN CONTAINS( Description, @SearchWord ) THEN 1
30           WHEN( @SearchWord IS NOT NULL AND
31                 CONTAINS( Description, @SearchWord ) ) THEN 1
32           WHEN( @SearchWord IS NOT NULL AND
33                 FREETEXT( Description, @SearchWord ) ) THEN 1
34           ELSE 0
35       END = 1;
36
37WITH  Data( value ) AS ( SELECT  0
38                         UNION ALL
39                         SELECT  1 )
40SELECT  CASE
41            WHEN MIN( value ) <= 0 THEN 0
42            WHEN MAX( 1 / value ) >= 100 THEN 1
43        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. 10 17
2 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 28 16
3 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 29 16
4 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 31 17
5 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 33 17
6 SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates. 42 17
See Also

Other Resources

Analysis Rules