FIX: SELECT Joining Table with a Complex Subquery Having an IN clause in the JOIN Fails with AV (299575)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
This article was previously published under Q299575
BUG #: 352054 (SHILOH_BUGS)
SYMPTOMS
When you execute a SELECT statement with an ANSI JOIN between a table and a subquery, if the subquery involves a complex ANSI JOIN with an IN clause, an access violation may occur.
The following error message appears in the SQL Server errorlogs:
SqlDumpExceptionHandler: Process xx generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Each subsequent execution of the query in this format will generate the following error:
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
RESOLUTIONTo resolve this problem, obtain the latest service pack for 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
WORKAROUND
Rewrite the query, moving the IN clause to the WHERE clause instead of the JOIN clause; for example:
SELECT * FROM titles titles
LEFT JOIN
(SELECT sales.title_id FROM sales
LEFT JOIN titles t1 ON sales.title_id = t1.title_id
WHERE t1.title_id IN
(
(SELECT title_id FROM titles)
UNION
(SELECT title_id FROM titles)
)
) sales ON titles.title_id = sales.title_id
STATUSMicrosoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.MORE INFORMATION
The following code demonstrates the problem:
SELECT * FROM titles titles
LEFT JOIN
(SELECT sales.title_id FROM sales
LEFT JOIN titles t1 ON sales.title_id = t1.title_id AND t1.title_id IN
(
(SELECT title_id FROM titles)
UNION
(SELECT title_id FROM titles)
)
) sales ON titles.title_id = sales.title_id
The following stack trace is generated with this AV:
Short Stack Dump 0
0045B717 Module(sqlservr+0005B717) (CFoldUtil::GetTreeHandleContext(class COptExpr *,class IMemObj *,class CTreeHandle * *)+00000015) 0
0045B6C5 Module(sqlservr+0005B6C5) (COptExpr::PexprFold(class IMemObj *,class COptExpr *)+0000004A) 0
00568E63 Module(sqlservr+00168E63) (CCvtTree::PexprBuildProject(class TREE *,class TREE *,class COptExpr *,unsigned short,class CTableMetadata *,class CRemapPvr *)+000004BC) 0
0059DD66 Module(sqlservr+0019DD66) (CCvtTree::PexprFromSubqueryPred(class TREE *,class TREE *)+000000EC) 0
0045D089 Module(sqlservr+0005D089) (CCvtTree::PexprFromScalar(class TREE *,class TREE *,int)+000003CD) 0
0045DC93 Module(sqlservr+0005DC93) (CCvtTree::PexprFromLogical(class TREE *,class TREE *,enum ELogOp)+00000102) 0
0045D0EA Module(sqlservr+0005D0EA) (CCvtTree::PexprFromScalar(class TREE *,class TREE *,int)+00000298) 0
0049986B Module(sqlservr+0009986B) (CCvtTree::PexprJoinedTable(class TREE *,class TREE *,class TREE *)+00000139) 0
007DBAED Module(sqlservr+003DBAED) (CCvtTree::PexprJoinedTable(class TREE *,class TREE *,class TREE *)+0000003D) 0
0049984A Module(sqlservr+0009984A) (CCvtTree::PexprJoinedTable(class TREE *,class TREE *,class TREE *)+00000076) 0
00562AD3 Module(sqlservr+00162AD3) (CCvtTree::PexprBuildFrom(class TREE *,class TREE *,int)+000005E2) 0
00564B6F Module(sqlservr+00164B6F) (CCvtTree::PexprFromCmdlist(class TREE * *,int)+000002B8) 0
0056455C Module(sqlservr+0016455C) (CCvtTree::PexprFromTree(class TREE *,class CTableMetadata *,class CRemapPvr *,int,class CRange *,int)+0000015D) 0
005642A9 Module(sqlservr+001642A9) (CCvtTree::PqryFromTree(class TREE *,class IMemObj *,class CRangeCollection *,unsigned long,class CCompPlan *)+0000026C) 0
00564019 Module(sqlservr+00164019) (BuildQueryFromTree(class TREE *,class IMemObj *,class IMemObj *,class IQueryObj * *,class CRangeCollection *,unsigned long,class CCompPlan *)+00000046) 0
00563F78 Module(sqlservr+00163F78) (CStmtQuery::InitQuery(class CAlgStmt *,class CCompPlan *,unsigned long)+0000014B) 0
0049DA48 Module(sqlservr+0009DA48) (CStmtSelect::Init(class CAlgStmt *,class CCompPlan *,class IBrowseMode *)+00000091) 0
00447078 Module(sqlservr+00047078) (CCompPlan::FCompileStep(class CAlgStmt *,class CStatement * *)+00000AE7) 0
004510FE Module(sqlservr+000510FE) (CProchdr::FCompile(class CCompPlan *,class CParamExchange *)+00000D15) 0
00415080 Module(sqlservr+00015080) (CSQLSource::FTransform(class CParamExchange *)+0000037C) 0
004592CE Module(sqlservr+000592CE) (CSQLStrings::FTransform(class CParamExchange *)+000001A8) 0
0041534F Module(sqlservr+0001534F) (CSQLSource::Execute(class CParamExchange *)+00000176) 0
00459A54 Module(sqlservr+00059A54) (language_exec(struct srv_proc *)+000003C8) 0
004175D8 Module(sqlservr+000175D8) (process_commands(struct srv_proc *)+000000E0) 0
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class UmsWorkQueue *)+00000264) 0
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)+000000BC) 0
7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE) 0
77E92CA8 Module(KERNEL32+00012CA8) (CreateFileA+0000011B)
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbBug kbfix kbSQLServ2000sp1fix KB299575 |
|---|
|