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

Article translations Article translations
Article ID: 277698 - View products that this article applies to.
This article was previously published under Q277698
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 58544 (SQLBUG_70)
Expand all | Collapse all

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.

Properties

Article ID: 277698 - Last Review: October 21, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB277698

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com