FIX: Assertion Raised When DELETE Is Performed on a Table Joined with a View (295039)

SYMPTOMS

An assertion may be raised when a DELETE is performed on a table and the following conditions are true:

• The DELETE is based on a join with a view.
• The table has a clustered index.
• The table is not empty (that is, has at least one record).

When the DELETE is run from Query Analyzer, the following message may be seen: The following assertion can also be seen in the SQL Server errorlog:

Short Stack Dump
77F97AC6 Module(ntdll+00017AC6) (ZwGetContextThread+0000000B)
0092569E Module(sqlservr+0052569E) (utassert_fail(enum UTASSERT_TYPE,char const *,char const *,int,char const *,...)+000002E9)
007FD074 Module(sqlservr+003FD074) (RecBase::MarkGhost(unsigned char *)+0000002F)
0041E485 Module(sqlservr+0001E485) (Page::MarkGhostRow(int,class XdesId const &)+0000001E) 
0041E7E6 Module(sqlservr+0001E7E6) (PageRef::DeleteRows(struct BUF *,int,int,enum ETabStatus,int,class XDES *,unsigned int)+000003B1) 
0041E44E Module(sqlservr+0001E44E) (PageRef::MarkIndexRowAsDeleted(struct BUF *,int,class SDES *)+00000052) 
0041EB8C Module(sqlservr+0001EB8C) (SDES::IndexDeleteRow(class Scan_rid &,int,int,int)+000001ED) 
00442DD0 Module(sqlservr+00042DD0) (RowsetSS::DeleteRows(unsigned long,unsigned long,unsigned long const * const,unsigned long * const)+00000161) 
004BFD41 Module(sqlservr+000BFD41) (CQScanUpdate::GetRow(unsigned long *,unsigned long *)+000000AB) 
00427985 Module(sqlservr+00027985) (CQueryScan::GetRow(unsigned long *,unsigned long *)+00000014) 
004271BA Module(sqlservr+000271BA) (CStmtQuery::ErsqExecuteQuery(class CMsqlExecContext *,class CEsComp const *,class CEsComp const *,unsigned long *,int,int)const +0000040D) 
0042EA36 Module(sqlservr+0002EA36) (CStmtDML::XretExecuteNormal(class CMsqlExecContext *)const +000002F0) 
0042E82D Module(sqlservr+0002E82D) (CStmtDML::XretExecute(class CMsqlExecContext *)const +0000001C) 
004160DB Module(sqlservr+000160DB) (CMsqlExecContext::ExecuteStmts(class ExecutionContext *)+0000027E) 
00415765 Module(sqlservr+00015765) (CMsqlExecContext::Execute(class CCompPlan *,class CParamExchange *)+000001C7) 
00415410 Module(sqlservr+00015410) (CSQLSource::Execute(class CParamExchange *)+00000343) 
00459A54 Module(sqlservr+00059A54) (language_exec(struct srv_proc *)+000003C8) 
004175D8 Module(sqlservr+000175D8) (process_commands(struct srv_proc *)+000000E0) 
410735D0 Module(UMS+000035D0) (ProcessWorkRequests(class UmsWorkQueue *)+00000264) 
4107382C Module(UMS+0000382C) (ThreadStartRoutine(void *)+000000BC) 7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE) 
77E92CA8 Module(KERNEL32+00012CA8) (CreateFileA+0000011B)
-------------------------------------------------------------------------------
2001-04-17 20:08:45.21 spid51    SQL Server Assertion: File: <recbase.cpp>, line = 1352 
Failed Assertion = 'FALSE' Invalid switch value.
2001-04-17 20:08:45.22 spid51    Error: 3624, Severity: 20, State: 1.
				

USE PUBS
GO

/****** Object:  Table [dbo].[state_id]    Script Date: 4/5/01 5:38:17 PM ******/ 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[state_id]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[state_id]
GO

/****** Object:  Table [dbo].[state_id]    Script Date: 4/5/01 5:38:17 PM ******/ 
CREATE TABLE [dbo].[state_id] (
	[sid] [int] NULL 
) ON [PRIMARY]
GO

/* The DELETE runs fine without any asserts if the table has no clustered index. */ 
CREATE  CLUSTERED  INDEX [clus_id] ON [dbo].[state_id]([sid]) ON [PRIMARY]
GO

SET NOCOUNT ON
/* The DELETE runs fine without any asserts if the table is empty. */ 
INSERT state_id VALUES(1)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[state_code]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[state_code]
GO

CREATE VIEW state_code(sid,name,code) AS
	SELECT 0, 'Texas', 'TX' UNION
	SELECT 1, 'California', 'CA' UNION
	SELECT 2, 'Washington', 'WA' UNION
	SELECT 3, 'Arizona', 'AZ' 

GO

BEGIN TRAN
DELETE state_id 
FROM state_id,state_code
ROLLBACK TRAN

/* Cleanup code */ 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[state_id]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table [dbo].[state_id] 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[state_code]') and OBJECTPROPERTY(id, N'IsView') = 1) 
drop view [dbo].[state_code] 
GO