DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 285870 - Last Review: October 28, 2003 - Revision: 3.1

This article was previously published under Q285870
BUG #: 101111 (SQLBUG_70)
BUG #: 351761 (SHILOH_bugs)

On This Page

SYMPTOMS

An UPDATE operation may not modify the correct number of rows if the following conditions are met:
  • Your query updates a column, ColA, from a table TabA.
  • The query references TabA again, using an alias TabB.
  • There is a reference to TabB.ColA somewhere in the query.
  • None of the columns being updated in TabA are indexed columns.
  • A hash or merge join is used.

CAUSE

The query plan may not include a necessary Table Spool below the UPDATE operator, which is necessary to provide Halloween Protection.

RESOLUTION

SQL Server 2000

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211  (http://support.microsoft.com/kb/290211/EN-US/ ) 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  (http://support.microsoft.com/kb/301511/EN-US/ ) INF: How to Obtain the Latest SQL Server 7.0 Service Pack


NOTE: The following hotfix was created prior to Microsoft SQL Server 7.0 Service Pack 4.

The English version of this fix should have the following file attributes or later:
   Version      File name       Platform
   -------------------------------------

   7.00.978     s70978i.exe     x86
   7.00.978     s70978a.exe     Alpha
				
NOTE: Because of file dependencies, the most recent hotfix or feature that contains the preceding files may also contain additional files.

WORKAROUND

To work around this problem:
  • Force a loop join by using OPTION(LOOP JOIN).

    -or-

  • Add an index on the updated columns.

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

Halloween protection is needed to prevent a situation where the physical location of a row within a table changes due to an UPDATE operation. As a result, the same row may be revisited multiple times within the context of a single logical operation, which should not occur. If the conditions for this particular bug are met, the SQL Server optimizer may not build a plan with appropriate Halloween protection.

The following example demonstrates the problem:
set nocount on
create table test(id int, pid int, fn varchar(256), rn varchar(8))
go
declare @c int
set @c = 1
insert into test values(0, NULL, 'root', 'root')
while @c < 10
begin
   insert into test values(@c, @c-1, NULL, cast(@c as varchar(8)))
   set @c = @c + 1
end
create unique clustered index idx_c_id on test(id)
go
update test
set fn = parent.fn + '/' + test.rn
from test(index=0) , test parent(index=0)
where test.pid = parent.id and test.fn is NULL --  and parent.fn <> ''
option(hash join,force order)
				
Here are the correct contents of the table after the update:

-- CORRECT results:
id          pid         rn       fn                                                                                                                                                                                                                                                               
----------- ----------- -------- ---- 
0           NULL        root     root
1           0           1        root/1
				
Here are the results you obtain prior to applying the fix:

-- INCORRECT results:
id          pid         rn       fn                                                                                                                                                                                                                                                               
----------- ----------- -------- ----
0           NULL        root     root
1           0           1        root/1
2           1           2        root/1/2
3           2           3        root/1/2/3
4           3           4        root/1/2/3/4
5           4           5        root/1/2/3/4/5
6           5           6        root/1/2/3/4/5/6
7           6           7        root/1/2/3/4/5/6/7
8           7           8        root/1/2/3/4/5/6/7/8
9           8           9        root/1/2/3/4/5/6/7/8/9

				

APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbbug kbfix KB285870
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support