Microsoft KB Archive/154353

= FIX: Aggregate Function on View With Correlated Subquery Can Cause Access Violation =

Article ID: 154353

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



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

Additional query words: AV SUM AVG COUNT MAX MIN

Keywords: kbbug kbfix kbnetwork KB154353

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.