Microsoft KB Archive/274165

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

Article ID: 274165

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q274165



BUG #: 236283 (Shiloh_bug)

BUG #: 58385 ( Sqlbug_70)



SYMPTOMS
A SELECT query against a view that was created by left joining four tables with &quot;*=&quot; syntax may return incorrect results.



WORKAROUND
Create the view by using ANSI syntax for the left outer joins.



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

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



MORE INFORMATION
The script that follows provides a scenario in which you can reproduce the problem: -- 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

Keywords: kbbug kbpending KB274165

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.