FIX: Server Side Cursor May Return Incorrect Results

This article was previously published under Q286788
This article has been archived. It is offered "as is" and will no longer be updated.
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.
RESOLUTION

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 theMicrosoft 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 theMicrosoft 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)goCREATE TABLE  TABLE2( T2COL1 int NOT NULL PRIMARY KEY, T2COL2 int NOT NULL )goCREATE TABLE TABLE3(	T3COL1 int NOT NULL)goInsert 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 T1open testfetch testfetch testdeallocate 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 T1open testfetch testfetch testdeallocate test				
SSrvTran_SQL
Properties

Article ID: 286788 - Last Review: 01/16/2015 21:31:28 - Revision: 3.2

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB286788
Feedback