Microsoft KB Archive/166632

= BUG: Outer Join VIEW with UNION ALL May Fail with AV =

Article ID: 166632

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q166632



BUG #: 16767



SYMPTOMS
A query may fail with an exception access violation (AV) when you do an OUTER JOIN to a VIEW that was created with the UNION ALL operator. On the client side, the application will receive the following error:

DB-Library Process Dead - Connection Broken

The following script demonstrates the problem:

CREATE TABLE dog (     id int   ) GO  CREATE TABLE cat (     id int   ) GO  CREATE TABLE fish (     id int   ) GO  CREATE VIEW pet AS   SELECT id   FROM dog UNION ALL SELECT id  FROM cat GO  SELECT p.id   FROM pet p, fish f   WHERE p.id *= f.id   GO



WORKAROUND
There are two possible workarounds for this problem. First, you can create a temporary table to hold the result set from the UNION ALL operator. The following script demonstrates this workaround:

SELECT id INTO #pet FROM dog UNION ALL SELECT id FROM cat GO

SELECT p.id FROM #pet p, fish f WHERE p.id *= f.id GO

The second workaround is to change the join syntax to ANSI syntax as follows:

SELECT p.id FROM pet p LEFT OUTER JOIN fish f ON p.id = f.id GO



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Keywords: kbbug kbpending kbusage KB166632

-

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

© Microsoft Corporation. All rights reserved.