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

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 "RESIDUAL:", 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

MORE INFORMATION

An an example, here is part of a query plan that has the characteristics defined in the "Symptoms" 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].