Microsoft KB Archive/250354

= FIX: Optimizer Generates Access Violation When Attempting to Create a Hash Join Plan =

Article ID: 250354

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q250354



BUG #: 57305(SQLBUG_70)



SYMPTOMS
In certain circumstances, the SQL Server optimizer may generate a handled Access Violation (AV) when attempting to create a Hash Join Plan. A sample of the table definition and the query that generates the AV follows: Use Pubs Go CREATE TABLE dbo.Tbl (   Col1 varchar(35) NOT NULL,    Col2 money NOT NULL ) Go SELECT T1.Col1 FROM ( SELECT T1.Col1, SUM(T1.Col2) AS Col2     FROM Tbl T1        GROUP BY T1.Col1  ) T1  FULL OUTER JOIN ( SELECT T2.Col1, SUM(T2.Col2) As Col2     FROM Tbl T2        GROUP BY T2.Col1  ) T2  ON T1.Col1 = T2.Col1 WHERE (T2.Col2 - T1.Col2) < 0 Go



WORKAROUND
Here are a few ways you can work around this problem:


 * Create a Clustered Index on Col1.


 * Issue a SET FORCEPLAN ON statement and then execute the SELECT.


 * Replace the FULL OUTER JOIN with a FULL OUTER MERGE JOIN or a FULL OUTER LOOP JOIN.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site:

http://support.microsoft.com/highlights/sql.asp

For more information, contact your primary support provider.



MORE INFORMATION
The following behavior was also observed:
 * Changing the datatype of Col2 to INT or NUMERIC does not change the behavior.


 * Using just the SHOWPLAN_ALL ON option or the STATISTICS PROFILE ON option also causes the AV. This is because the AV occurs when the optimizer tries to create an execution plan.


 * Changing the operator in the WHERE clause does not change the behavior.


 * Removing the WHERE clause ( WHERE (T2.Col2 - T1.Col2) < 0 ) does not cause the AV.


 * The number of rows present in the table also influences the plan the optimizer chooses. In this case, a larger number of rows causes the optimizer to consider a Hash join resulting in an AV.

Keywords: kbbug kbfix KB250354

-

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

© Microsoft Corporation. All rights reserved.