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

Last reviewed: February 3, 1998
Article ID: Q180300
The information in this article applies to:
  • Microsoft SQL Server, version 6.5
BUG #: 17770

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

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

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.


Additional query words:
Keywords : kbbug6.50 SSrvTran_SQL
Version : WINNT:6.5
Platform : winnt
Issue type : kbbug
Solution Type : kbworkaround


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 3, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.