A DELETE statement with a join that involves views may not delete all the target rows in SQL Server 2000 Service Pack 3 (833376)

MORE INFORMATION

Steps to reproduce the behavior

1. Start SQL Query Analyzer.
2. Connect to the pubs database in SQL Server.
3. Run the following Transact-SQL statements:

   -- Create a test table and name it MyTable
   create table MyTable (FirstCol int)
   go
   	
   --  Create a view and name it FirstView
   create view FirstView as
    select MyTable.FirstCol as FirstCol, T.Col1 as SecondCol
    from MyTable, (select 1 as Col1 union all select 2) T
   go
   	
   --  Create a view and name it SecondView
   create view SecondView as	
    select FirstView.FirstCol as SecondCol,
    FirstView.SecondCol as FirstCol
    from FirstView, MyTable
   go
   	
   --  Insert sample rows in the MyTable table
   insert into MyTable values (1)
   insert into MyTable values (2)
   insert into MyTable values (2)
   go
   		
   --  Delete rows in the MyTable table by joining the table with the FirstView and the SecondView views
   delete from MyTable
    from FirstView, SecondView
    where MyTable.FirstCol = FirstView.SecondCol
    and SecondView.FirstCol = FirstView.FirstCol
    and SecondView.SecondCol = 1
   go
   	
   --  Verify the rows in the MyTable table	
   select * from MyTable
   go

   Note One row is not deleted from the MyTable table.