BUG: Aggregate Function on View With Correlated Subquery Can AV

ID: Q154353

The information in this article applies to:

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

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

Additional query words: AV SUM AVG COUNT MAX MIN


Keywords          : kbnetwork SSrvGen SSrvProg 
Version           : 6.5
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 31, 1999