Microsoft KB Archive/250354

From BetaArchive Wiki
Knowledge Base


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:

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