Knowledge Base

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

Article ID: 166632

Article Last Modified on 10/3/2003

Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q166632

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