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

• Microsoft SQL Server, version 6.5

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

   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

To work around this problem, you can create a temporary table to hold the result set from the UNION ALL operator. The following script demonstrates the workaround for the above scenario:

   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

STATUS