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
