BUG: Aggregate Functions in a CASE Expression in a Correlated Subquery May Cause an Access Violation (180300)

SYMPTOMS

A query that contains two or more aggregate functions within a CASE expression contained within a correlated subquery may cause a thread level access violation (AV).

Any combination of the MIN(), MAX(), or SUM() functions could cause the problem in this scenario. The COUNT(), COUNT(*), and AVG() functions do not exhibit this behavior.

Note that a query that meets the problem criteria outlined above may not cause the AV if there is less data in the queried tables.

WORKAROUND

To work around this problem, break the query into two separate SQL statements. The first query should collect the required information into a temporary table; the second can then produce the final result set.

For example, suppose you had the following query, which causes this problem:

   SELECT anl.id, anl.type, production =
      (SELECT
         CASE
            WHEN SUM(production) >99999 THEN 1
            WHEN SUM(production) <=99999  AND SUM(production) > 500 THEN 2
            ELSE 3
         END
         FROM    anl_ar
         WHERE anl_ar.id = anl.id
         AND    anl_ar.type = anl.type
      )
      FROM    lan, anl, tak
      WHERE anl.lan = lan.lan
      AND    tak.tak_id = anl.tak_id

To avoid the problem, you can rewrite the above query as:

   SELECT anl.id, anl.type, production_sum =
      (SELECT SUM(production)
         FROM    anl_ar
         WHERE anl_ar.id = anl.id
         AND    anl_ar.type = anl.type
      )
      INTO    #temp1
      FROM   lan, anl, tak
      WHERE   anl.lan = lan.lan
      AND   tak.tak_id = anl.tak_id

   SELECT id, type, production =
      CASE
         WHEN production_sum >99999 THEN 1
         WHEN production_sum <=99999  AND production_sum > 500 THEN 2
         ELSE 3
      END
      FROM #temp1

BUG: Aggregate Functions in a CASE Expression in a Correlated Subquery May Cause an Access Violation (180300)

SYMPTOMS

CAUSE

WORKAROUND

STATUS