Microsoft KB Archive/277698

= FIX: LEFT OUTER JOIN on a View that Uses the DISTINCT Keyword Produces Incorrect Results =

Article ID: 277698

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q277698



BUG #: 58544 (SQLBUG_70)



SYMPTOMS
A SELECT query that contains a LEFT OUTER JOIN on a nested view, which uses the DISTINCT keyword, produces incorrect results in SQL Server 7.0. For example: use pubs go set nocount on go CREATE TABLE [A] (   [exch_server] [varchar] (10) NOT NULL,    [note_id] [varchar] (10) NOT NULL ,    [entity_id] [varchar] (10) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [B] (   [note_id] [varchar] (10) NOT NULL,    [entity_id] [varchar] (10) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [C] (   [channel_entity_id] [varchar] (10) NOT NULL,    [entity_id] [varchar] (10) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [D] (   [exch_server] [varchar] (10) NOT NULL,    [entity_id] [varchar] (10) NOT NULL ) ON [PRIMARY] GO CREATE VIEW viewE AS SELECT DISTINCT D.exch_server , C.entity_id FROM C INNER JOIN D             ON C.channel_entity_id = D.entity_id GO CREATE VIEW viewF AS SELECT viewE.exch_server , B.note_id , B.entity_id FROM viewE INNER JOIN B    ON viewE.entity_id = B.entity_id GO INSERT [A] VALUES ('testA','1','5') GO INSERT [B] VALUES ('1','5') GO INSERT [C] VALUES ('2','5') GO INSERT [D] VALUES ('testB','2') GO set nocount off GO

SELECT A.note_id ,A.entity_id ,viewF.exch_server FROM A LEFT OUTER JOIN viewF ON A.note_id = viewF.note_id Where viewF.exch_server IS NULL GO

-- The preceding query returns one row instead of zero rows.

DROP TABLE [A] GO DROP TABLE [B] GO DROP TABLE [C] GO DROP TABLE [D] GO DROP VIEW viewF GO DROP VIEW viewE GO



WORKAROUND
To work around this problem, use any of the following methods:
 * Remove the DISTINCT keyword from viewE.
 * Make either viewE or viewF a permanent table.
 * Make both viewE and viewF permanent tables.
 * Use nested selects and derived tables in the final query.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

Keywords: kbbug kbfix KB277698

-

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

© Microsoft Corporation. All rights reserved.