FIX: Outer Join on View with count() GP Faults SQL

  - Microsoft SQL Server version 4.2 for OS/2

SYMPTOMS

Under certain circumstances, executing a query that does an outer join between a table and a view can cause SQL Server to general protection fault (GP fault).

CAUSE

If the view contains a count() function and a GROUP BY clause, and the query references the column in the count() function, the server will GP fault.

The following script is an example of this problem:

   use pubs
   go

   create view pubtype
   as
   select pub_id, quantity=count(title)
   from titles
   group by pub_id
   go

   select T.type, P.quantity
   from titles T, pubtype P
   where T.pub_id *= P.pub_id
     and P.pub_id = '0877'
   go

   < The server will GP fault >

WORKAROUND

Any of the following variations to the view/query can be used to avoid the problem:

1. Omit "and P.pub_id = '0877' ".

2. Omit "quantity=count(titles)" from the view.

3. Omit the "group by pub_id".

4. Use an equijoin rather than an outer join.

5. Use "and T.pub_id = '0877' " rather than "and P.pub_id...".

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2. This problem has been corrected in version 4.2a. For more information, please contact your primary support provider.