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

Introduction

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:

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;

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:

WITH PreComputedValues (minValue, maxValue, safeDivision) AS
(
    SELECT MIN(value), MAX(1.0 / NULLIF(value, 0)), 100 AS safeDivision
    FROM Data
)
SELECT
   CASE
      WHEN minValue <= 0 THEN 0
      WHEN maxValue >= safeDivision THEN 1
   END
FROM 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.

Additional Information

CASE (Transact-SQL)

COALESCE (Transact-SQL)

Dirty Secrets of the CASE Expression

SQL Server - Full Text Search and the problem with null/empty predicates

Don’t depend on expression short circuiting in T-SQL (not even with CASE)

Example Test SQL

DECLARE  @v AS INT = 0

SELECT  CASE
            WHEN @v = 0 THEN 1
            ELSE( SELECT  MIN( 1 / @v ) )
        END;

SELECT  CASE
            WHEN @v = 0 THEN 1
            ELSE MAX( 1 / @v )
        END;

DECLARE  @SearchWord AS NVARCHAR( 30 ) = ''

SELECT  Description
FROM  Production.ProductDescription
WHERE  CASE
           WHEN @SearchWord = '' OR
           @SearchWord IS NULL THEN 1
           WHEN 1 = ( SELECT TOP( 1 )
                              1
                      FROM  Production.ProductDescription
                      WHERE  FREETEXT( Description, @SearchWord ) ) THEN 1
           WHEN 1 = ( SELECT TOP( 1 )
                              1
                      FROM  Production.ProductDescription
                      WHERE  CONTAINS( Description, @SearchWord ) ) THEN 1
           WHEN FREETEXT( Description, @SearchWord ) THEN 1
           WHEN CONTAINS( Description, @SearchWord ) THEN 1
           WHEN( @SearchWord IS NOT NULL AND
                 CONTAINS( Description, @SearchWord ) ) THEN 1
           WHEN( @SearchWord IS NOT NULL AND
                 FREETEXT( Description, @SearchWord ) ) THEN 1
           ELSE 0
       END = 1;

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

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

Analysis Rules