Microsoft KB Archive/299575

= FIX: SELECT Joining Table with a Complex Subquery Having an IN clause in the JOIN Fails with AV =

Article ID: 299575

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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



RESOLUTION
To 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



STATUS
Microsoft 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)

Keywords: kbbug kbfix kbsqlserv2000sp1fix KB299575

-

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

© Microsoft Corporation. All rights reserved.