Microsoft KB Archive/328187

= FIX: A Query That Uses a Hash Match with a Residual Predicate Might Return Incorrect Results =

Article ID: 328187

Article Last Modified on 9/27/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q328187



BUG #: 361786 (SHILOH_BUGS)



SYMPTOMS
A query that meets all these conditions might return incorrect results:
 * The query performs a hash join.
 * The hash join is executed multiple times (the Executes column for the Hash Match operator in the STATISTICS PROFILE output will be greater than 1).
 * The hash join has a residual predicate (in the STATISTICS PROFILE output, the Hash Match operator will include a predicate after the text &quot;RESIDUAL:&quot;, and this predicate will contain additional conditions that are not part of the join condition that is listed before the residual predicate).



RESOLUTION
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

Hotfix
Note The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.   Date         Time   Version         Size             File name -  19-Aug-2002  19:42  2000.80.678.0   7,471,185 bytes  Sqlservr.exe Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

The following article describes a different problem that has similar symptoms and is difficult to distinguish from this issue. Because the fix described in Microsoft Knowledge Base article 331158 also includes the fix for the issue described in this article, if you are experiencing a problem similar to this one you may want to apply the hotfix from 331158 instead of the one in this article. That way, you receive the benefit of both fixes.

331158 FIX: A Query That Causes a Hash Bailout May Return Incorrect Results



WORKAROUND
Because the problem is specific to certain hash operations, you may avoid the problem by adding an &quot;OPTION (LOOP JOIN, MERGE JOIN)&quot; hint to the end of the query.



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

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



MORE INFORMATION
An an example, here is part of a query plan that has the characteristics defined in the &quot;Symptoms&quot; section:   Rows   Execs  StmtText --  ...   7500   15     |--Hash Match(Inner Join, HASH:(([C].[Serial])=([CB].[Serial]),RESIDUAL:(([C].[DateSubmitted]>[CB].[DateSubmitted])   ... As you can see, this is an Inner Hash Join that is executed multiple times (15 times to be specific. See the Execs column), and it has a residual predicate of [C].[DateSubmitted]>[CB].[DateSubmitted] that is not part of the Join condition [C].[Serial])=([CB].[Serial].

Keywords: kbbug kbfix kbsqlserv2000presp3fix kbqfe kbsqlserv2000sp3fix kbhotfixserver KB328187

-

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

© Microsoft Corporation. All rights reserved.