BUG: Access Violation Occurs on DECLARE CURSOR with Correlated Subquery and KEYSET or DYNAMIC Cursor Type (282831)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q282831
BUG #: 55675 (SQLBUG_70)

SYMPTOMS

An Access Violation (AV) may occur on an sp_cursoropen or DECLARE CURSOR statement if the following conditions are met:
  • A SELECT statement involves a correlated subquery.

  • The correlation is through either an IN or an OR clause, with at least two different columns to compare to on the right side.

  • A non-clustered primary key is on both correlated tables.

  • The subquery table does not have any additional clustered indexes.

  • The cursor type is KEYSET or DYNAMIC. This problem does not occur with INSENSITIVE, STATIC, or FAST_FORWARD cursors.
The SELECT statement works fine outside the cursor.

WORKAROUND

To work around this behavior:
  • Replace PRIMARY KEY NONCLUSTERED with PRIMARY KEY CLUSTERED. -or-

  • Add a clustered index to the correlated subquery table. -or-

  • Change the cursor type to STATIC, FAST_FORWARD or INSENSITIVE. -or-

  • Rewrite the SELECT statement to avoid the correlated subquery.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.

MORE INFORMATION

Here is an example of a stack dump that you may find in the SQL Server error log file that corresponds to this particular bug: 
2000-11-28 10:54:06.39 spid9    Error: 0, Severity: 19, State: 0
2000-11-28 10:54:06.39 spid9    SqlDumpExceptionHandler: Process 9 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
.
*******************************************************************************
*
* BEGIN STACK DUMP:
*   11/28/00 10:54:06 spid 9
*
*   Exception Address = 006A4627 (CSubRuleCrsFtchToUnionAllStrategy::BuildSubstitutes + f6)
*   Exception Code    = c0000005 E
*   Access Violation occurred reading address 0000004E

Short Stack Dump
0x006a4627 Module(sqlservr+2a4627) (CSubRuleCrsFtchToUnionAllStrategy::BuildSubstitutes+f6)
0x004f96a6 Module(sqlservr+f96a6) (COptContext::PexprTransformTopLevel+199)
0x004f93c5 Module(sqlservr+f93c5) (COptContext::PexprNormalize+22)
0x004f949b Module(sqlservr+f949b) (COptContext::PexprNormalize+a0)
0x004f949b Module(sqlservr+f949b) (COptContext::PexprNormalize+a0)
0x004f949b Module(sqlservr+f949b) (COptContext::PexprNormalize+a0)
0x004f8358 Module(sqlservr+f8358) (CMemo::NormalizeQuery+174)
0x004f7fdf Module(sqlservr+f7fdf) (COptContext::PexprSearchPlan+bb)
0x00447eef Module(sqlservr+47eef) (COptContext::PcxteOptimizeQuery+7eb)
0x0044693b Module(sqlservr+4693b) (CQuery::Optimize+21f)
0x004464b3 Module(sqlservr+464b3) (CQuery::Prepare+92)
0x0044b6bd Module(sqlservr+4b6bd) (CCvtTree::PqryFromTree+814)
0x0044b3be Module(sqlservr+4b3be) (BuildQueryFromTree+61)
0x005d863e Module(sqlservr+1d863e) (CFetchKeyset::CompileQuery+60)
0x005d8a81 Module(sqlservr+1d8a81) (CFetchKeyset::CompileSetFetchExp+56c)
0x005d8e8c Module(sqlservr+1d8e8c) (CFetchKeyset::CompileKeysetExprs+18)
0x005d8ff0 Module(sqlservr+1d8ff0) (CFetchKeyset::Compile+10f)
0x005d62ee Module(sqlservr+1d62ee) (CFetchComponent::smInitializeFetchComponent+fc)
0x005c47d2 Module(sqlservr+1c47d2) (CCursorDeclareStmt::Init+286)
0x005c6c1e Module(sqlservr+1c6c1e) (CompileCursor+1f8)
0x00530f0e Module(sqlservr+130f0e) (CCompPlan::FCompileStep+e79)
0x0043d00f Module(sqlservr+3d00f) (CProchdr::FCompile+5d9)
0x0040ec53 Module(sqlservr+ec53) (CSQLSource::FTransform+234)
0x00456463 Module(sqlservr+56463) (CSQLStrings::FTransform+159)
0x0040e7b9 Module(sqlservr+e7b9) (CSQLSource::Execute+11d)
0x00456187 Module(sqlservr+56187) (language_exec+39c)
0x41061253 Module(opends60+1253) (execute_event+659)
0x4106144e Module(opends60+144e) (process_commands+f3)
0x41092a15 Module(ums+2a15) (ProcessWorkRequests+ed)
0x410932cb Module(ums+32cb) (ThreadStartRoutine+139)
0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce)
0x77f04ede Module(KERNEL32+4ede) (lstrcmpiW+be)
-------------------------------------------------------------------------------
2000-11-28 10:54:07.52 spid9    Error: 0, Severity: 19, State: 0
2000-11-28 10:54:07.52 spid9    language_exec: Process 9 generated an access violation. SQL Server is terminating this process.
.
Modification Type:MajorLast Reviewed:10/16/2002
Keywords:kbBug kbDSupport KB282831
©2002 Microsoft Corporation. All rights reserved.