PRB: SELECT on a VIEW Generates an Access Violation When an 'IN' or 'OR' Clause Contains More Than 19 Values (319054)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions) SP2
This article was previously published under Q319054

SYMPTOMS

In Microsoft SQL Server 2000 Service Pack 2 (SP2), an Access Violation (AV) occurs when you SELECT from a VIEW using an 'IN' or 'OR' condition that contains more than 19 values. Additionally, the view definition must meet both of the following conditions for you to receive the AV:
  • The view contains a UNION operation.
  • One of the SELECT statements in that UNION must contain a value in the select list instead of a column name.
This problem does not occur on SQL Server 2000 Release to Manufacturing (RTM) and SQL Server 2000 Service Pack 1 (SP1). You may also see the problem if you have upgraded to SP2 from either RTM or SP1.


You receive the following error message if you run the SELECT statement in Query Analyzer:
ODBC: Msg 0, Level 19, State 1 SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Connection Broken
SQL Server generates the following stack dump in the Errorlog file. This stack dump example is from SQL Server 2000 SP2: 
* Short Stack Dump     
* 0074E0DD Module(sqlservr+0034E0DD)(CScaOp_In::CalcSelectivity+000000C2)
* 0074B3EE Module(sqlservr+0034B3EE) (GenerateInListAndSelectivity+0000024A)   
* 0074B4A0 Module(sqlservr+0034B4A0) (CScaOp_Logical::CalcSelectivity_lopOr+0000017B)                            
* 004511D7 Module(sqlservr+000511D7) (CScaOp_Logical::CalcSelectivity+000000A7)
* 004F7EF5 Module(sqlservr+000F7EF5) (CalculateFilter+000000AF)
* 004F7BCC Module(sqlservr+000F7BCC) (CSelContext::CalculateSelectivity+00000261)                                
* 004F793F Module(sqlservr+000F793F) (CalcCard+000003CA)                    
* 004F7662 Module(sqlservr+000F7662) (GroupCardFromSel+000001A1)            
* 005412DD Module(sqlservr+001412DD) (ComputeGroupProperties+000000CB)      
* 004FBF35 Module(sqlservr+000FBF35) (CLogOp_Select::DeriveGroupProperties+0000006B)                             
* 00540BC2 Module(sqlservr+00140BC2) (COpArg::DeriveNormalizedGroupProperties+0000001B)              
* 00540B4E Module(sqlservr+00140B4E) (COptExpr::DeriveGroupProperties+000000B3)
* 00540AF8 Module(sqlservr+00140AF8) (COptExpr::DeriveGroupProperties+0000005D)
* 005534DD Module(sqlservr+001534DD) (COptContext::PexprTransformTopLevel+000001CD)                              
* 00553074 Module(sqlservr+00153074) (COptContext::PexprNormalize+00000034) 
* 00553016 Module(sqlservr+00153016) (CMemo::NormalizeQuery+0000006E)       
* 00552EDB Module(sqlservr+00152EDB) (COptContext::PexprSimplify+000000E8)  
* 005525B4 Module(sqlservr+001525B4) (COptContext::PcxteOptimizeQuery+000004C5)
* 00551A3A Module(sqlservr+00151A3A) (CQuery::Optimize+000003F9)            
* 00551815 Module(sqlservr+00151815) (CQuery::Optimize+00000030)            
* 0054C482 Module(sqlservr+0014C482) (CCvtTree::PqryFromTree+0000029D)      
* 0054C230 Module(sqlservr+0014C230) (BuildQueryFromTree+00000046) 
* 0054C19D Module(sqlservr+0014C19D) (CStmtQuery::InitQuery+0000013E)       
* 004FF9C9 Module(sqlservr+000FF9C9) (CStmtSelect::Init+0000008A)           
* 0053546E Module(sqlservr+0013546E) (CCompPlan::FCompileStep+00000AD6)     
* 0053BD8F Module(sqlservr+0013BD8F) (CProchdr::FCompile+00000D23)          
* 004106AD Module(sqlservr+000106AD) (CSQLSource::FTransform+0000034C)      
* 0053C893 Module(sqlservr+0013C893) (CSQLStrings::FTransform+000001A1)     
* 00410092 Module(sqlservr+00010092) (CSQLSource::Execute+0000015B)         
* 0053C498 Module(sqlservr+0013C498) (language_exec+000003E1)               
* 00411099 Module(sqlservr+00011099) (process_commands+000000EC)            
* 41073379 Module(UMS+00003379) (ProcessWorkRequests+0000024A)              
* 41073071 Module(UMS+00003071) (ThreadStartRoutine+000000BD)               
* 7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE)                  
* 77E92CA8 Module(KERNEL32+00012CA8) (BaseThreadStart+00000052)

WORKAROUND

To work around this behavior:
  • Break the SELECT query into a UNION of several SELECT statements where each SELECT contains less than 19 values in the IN clause, or less than 19 OR clauses.
  • Instead of using a view, create a temporary table.
For more details on these workarounds, please see the "More Information" section in this article.

STATUS

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

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Create two tables (tbl1 and tbl2) in the pubs database:
    USE pubs
GO
CREATE TABLE [tbl1] (
	[tbl1_col1] [int] ,
	[tbl1_col2] [char] (10)
) ON [PRIMARY]
GO

CREATE TABLE [tbl2] (
	[tbl2_col1] [int] ,
	[tbl2_col2] [char] (10)
) ON [PRIMARY]
GO
  2. Create the view using the UNION operator and also one of the select lists must contain an expression (the following example uses NULL):
    Create View Tables_Union as
Select tbl1_col1, tbl1_col2 from tbl1
  Union
Select tbl2_col1, NULL from tbl2
  3. A SELECT on this view with 19 or less search values runs fine.
    select * from Tables_Union where
tbl1_col2 in
('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19')
GO
  4. The following query raises the exception. The column that is referenced by the IN clause is a column that is specified in the select list of the view (tbl1_col2) and the IN clause contains more than 19 values:
    select * from Tables_Union where 
tbl1_col2 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20')
GO
  5. You can also raise the exception by using the OR clause.
    select * from Tables_Union where
tbl1_col2 = '1' or  tbl1_col2 = '2' or tbl1_col2 = '3' or tbl1_col2 = '4' or tbl1_col2 = '5' or tbl1_col2 = '6' or tbl1_col2 = '7' or tbl1_col2 = '8' or tbl1_col2 = '9' or tbl1_col2 = '10' or tbl1_col2 = '11' or 
tbl1_col2 = '12' or tbl1_col2 = '13' or tbl1_col2 = '14' or tbl1_col2 = '15' or tbl1_col2 = '16' or tbl1_col2 = '17' or tbl1_col2 = '18' or tbl1_col2 = '19' or tbl1_col2 = '20'
GO
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbpending kbprb KB319054 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.