Microsoft KB Archive/285870

= FIX: Update With Self Join May Update Incorrect Number Of Rows =

Article ID: 285870

Article Last Modified on 10/28/2003

-

APPLIES TO


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

-



This article was previously published under Q285870



BUG #: 101111 (SQLBUG_70)

BUG #: 351761 (SHILOH_bugs)



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.



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 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

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

Keywords: kbbug kbfix KB285870

-

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

© Microsoft Corporation. All rights reserved.