FIX: LEFT OUTER JOIN on a View that Uses the DISTINCT Keyword Produces Incorrect Results
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)
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 pubsgoset nocount ongoCREATE TABLE [A] ( [exch_server] [varchar] (10) NOT NULL , [note_id] [varchar] (10) NOT NULL , [entity_id] [varchar] (10) NOT NULL ) ON [PRIMARY]GOCREATE TABLE [B] ( [note_id] [varchar] (10) NOT NULL , [entity_id] [varchar] (10) NOT NULL ) ON [PRIMARY] GOCREATE TABLE [C] ( [channel_entity_id] [varchar] (10) NOT NULL , [entity_id] [varchar] (10) NOT NULL ) ON [PRIMARY]GOCREATE TABLE [D] ( [exch_server] [varchar] (10) NOT NULL , [entity_id] [varchar] (10) NOT NULL ) ON [PRIMARY]GOCREATE VIEW viewEASSELECT DISTINCT D.exch_server , C.entity_id FROM C INNER JOIN D ON C.channel_entity_id = D.entity_idGOCREATE VIEW viewFASSELECT viewE.exch_server , B.note_id , B.entity_id FROM viewE INNER JOIN B ON viewE.entity_id = B.entity_idGOINSERT [A] VALUES ('testA','1','5')GOINSERT [B] VALUES ('1','5')GO INSERT [C] VALUES ('2','5')GOINSERT [D] VALUES ('testB','2')GOset nocount offGOSELECT 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 NULLGO-- The preceding query returns one row instead of zero rows.<BR/>DROP TABLE [A]GODROP TABLE [B]GODROP TABLE [C]GODROP TABLE [D]GODROP VIEW viewFGODROP VIEW viewEGO
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.
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.0For more information, contact your primary support provider.
Article ID: 277698 - Last Review: 10/21/2013 02:52:37 - Revision: 2.1
Microsoft SQL Server 7.0 Standard Edition
- kbnosurvey kbarchive kbbug kbfix KB277698