Microsoft KB Archive/277698

Knowledge Base

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.<BR/>

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

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.