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

ID: Q166632

The information in this article applies to:

Microsoft SQL Server version 6.5

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 work arounds 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 work around is to change the join syntax to ANSI syntax as follows:


SELECT p.id
FROM pet p
RIGHT 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          : kbusage SSrvTran_SQL kbbug6.50 
Version           : 6.5
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 9, 1999