BUG: Select Query Against a View That Contains Left Outer Joins May Return Incorrect Results (274165)

MORE INFORMATION

-- Run the following script to create base tables and the view:

USE pubs
GO

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

CREATE TABLE [dbo].[TEST1] (
	[TEST1_ID] [int] NOT NULL ,
	[TEST2_ID] [int] NULL ,
	[TEST3_ID] [int] NULL ,
	[TEST4_ID] [int] NULL ,
	[DESC1] [char] (20) NULL 
) 
GO

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

CREATE TABLE [dbo].[TEST2] (
	[TEST2_ID] [int] NOT NULL ,
	[DESC2] [char] (20) NULL 
) 
GO

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

CREATE TABLE [dbo].[TEST3] (
	[TEST3_ID] [int] NOT NULL ,
	[DESC3] [char] (20) NULL 
) 
GO

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

CREATE TABLE [dbo].[TEST4] (
	[TEST4_ID] [int] NOT NULL ,
	[DESC4] [char] (20) NULL 
) 
GO

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

CREATE VIEW TEST_VIEW
   (TEST1_ID,
    TEST2_ID,
    TEST3_ID,
    TEST4_ID,
	DESC1,
	DESC2,
	DESC3,
	DESC4)
AS SELECT
    A.TEST1_ID,
    B.TEST2_ID,
    C.TEST3_ID,
    D.TEST4_ID,
	DESC1,
	DESC2,
	DESC3,
    DESC4
FROM TEST1 A, TEST2 B,
	 TEST3 C, TEST4 D
WHERE A.TEST2_ID *= B.TEST2_ID
AND   A.TEST3_ID *= C.TEST3_ID
AND   A.TEST4_ID *= D.TEST4_ID

GO

-- Populate base tables:

INSERT INTO TEST1 VALUES ( 1, NULL, NULL, NULL, 'TEST 1A' )
INSERT INTO TEST1 VALUES ( 2, 2, NULL, NULL, 'TEST 1B' )
INSERT INTO TEST1 VALUES ( 3, NULL, 3, NULL, 'TEST 1C' )
INSERT INTO TEST1 VALUES ( 4, NULL, NULL, 4, 'TEST 1D' )
INSERT INTO TEST1 VALUES ( 5, 1, 3, NULL, 'TEST 1C' )
INSERT INTO TEST2 VALUES ( 1, 'TEST 2A' )
INSERT INTO TEST2 VALUES ( 2, 'TEST 2B' )
INSERT INTO TEST3 VALUES ( 1, 'TEST 3A' )
INSERT INTO TEST3 VALUES ( 2, 'TEST 3B' )
INSERT INTO TEST3 VALUES ( 3, 'TEST 3C' )
INSERT INTO TEST4 VALUES ( 1, 'TEST 4A' )
INSERT INTO TEST4 VALUES ( 2, 'TEST 4B' )
INSERT INTO TEST4 VALUES ( 3, 'TEST 4C' )
INSERT INTO TEST4 VALUES ( 4, 'TEST 4D' )
GO

-- Run the following queries:
-- The first query returns five rows:
SELECT * FROM TEST_VIEW
GO

-- This query should return one row, but returns five rows instead:
SELECT * FROM TEST_VIEW WHERE DESC2 = 'TEST 2B'
GO