FIX: Aggregate Function on View With Correlated Subquery Can Cause Access Violation (154353)

Microsoft SQL Server 6.5

This article was previously published under Q154353

Bug #: 15724 (6.50)

SYMPTOMS

When you use an aggregate function on a view which has a correlated subquery preceded by the 'equal to' comparison operator, a handled access violation error occurs.

RESOLUTION

To work around this problem, rewrite the correlated subquery in the View as a join, or use 'in' instead of '=' before the subquery.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem was first corrected in Microsoft SQL Serverversion 6.5 Service Pack 2.

MORE INFORMATION

Consider the following script:

   create table X (xCol1 int not null,
                   xCol2 int not null)
   go
   create table Y (yCol1 int not null)
   go
   create view vX
   as
      select xCol1, xCol2
      from X
      where xCol1 =
         (select yCol1
         from Y
         where yCol1 = xCol1)
   go

The following query causes a handled access violation:

   select max(xCol2),
   xCol1
   from vX
   group by xCol1

To work around this problem, vX could have been defined as:

   create view vX
   as
      select xCol1, xCol2
      from X
      where xCol1 in   -- using 'in' instead of '='
         (select yCol1
         from Y
         where yCol1 = xCol1)

 -or-

   create view vX   -- rewriting, using a join instead
   as
      select xCol1, xCol2
      from X,Y
      where xCol1 = yCol1

Modification Type:

Minor

Last Reviewed:

3/14/2005

Keywords:

kbbug kbfix kbnetwork KB154353