BUG: Agg. Functions in CASE Exp. in Correlated Subquery May AV

• Microsoft SQL Server, version 6.5

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.

CAUSE

This problem can occur if the correlated subquery is in the SELECT list or in the WHERE clause.

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

   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

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.