BUG: Aggregate Function on View With Correlated Subquery Can AV

• Microsoft SQL Server, version 6.5

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.

WORKAROUND

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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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

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

   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