Microsoft KB Archive/286788

= FIX: Server Side Cursor May Return Incorrect Results =

Article ID: 286788

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q286788



BUG #: 351513 (Shiloh_bugs)

BUG #: 101092 (SQLBUG_70)



SYMPTOMS
A dynamic or keyset-driven, server side cursor may return incorrect results if all the following conditions are met:
 * The query uses a Sub-SELECT statement as a column list.
 * The Sub-SELECT uses the COUNT(*) function.
 * The Sub-SELECT has an ANSI Join with a condition.
 * The outer FROM-clause table has a Transact-SQL join with a Sub-SELECT.



SQL Server 2000
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

SQL Server 7.0
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

SQL Server 2000

This problem was first corrected in SQL Server 2000 Service Pack 1.

SQL Server 7.0

This problem was first corrected in Microsoft SQL Server 7.0 Service Pack 4.



MORE INFORMATION
To reproduce the problem, run this code: CREATE TABLE TABLE1( T1COL1 int NOT NULL PRIMARY KEY) go CREATE TABLE TABLE2( T2COL1 int NOT NULL PRIMARY KEY, T2COL2 int NOT NULL ) go

CREATE TABLE TABLE3(   T3COL1 int NOT NULL) go Insert into TABLE1(T1COL1) values(4) Insert into TABLE1(T1COL1) values(3) Insert into TABLE2(T2COL1, T2COL2) values(5, 3) Insert into TABLE2(T2COL1, T2COL2) values(6, 4) Insert into TABLE3(T3COL1) values(6) go

-- REPRO SCRIPT (Correct result would be 0,1) - declare test cursor keyset for Select (select count(*)     from dbo.TABLE2 as T2 join dbo.TABLE3 as T3 on T3.T3COL1 = T2.T2COL1      where T2.T2COL2 = T1.T1COL1) as Para from dbo.TABLE1 as T1 open test fetch test fetch test deallocate test

--- Workaround (Join condition moved to WHERE clause) declare test cursor keyset local for Select (select count(*)     from dbo.TABLE2 as T2, dbo.TABLE3 as T3       where T2.T2COL2 = T1.T1COL1 and T3.T3COL1 = T2.T2COL1) as Para from dbo.TABLE1 as T1

open test fetch test fetch test deallocate test

Additional query words: SSrvTran_SQL

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB286788

-

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

© Microsoft Corporation. All rights reserved.