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

• Microsoft SQL Server for OS/2, version 4.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.

Additional query words: outer join group by Transact-SQL

Keywords          : kbprg kbbug4.20 kbbug4.20a SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        : 

Last Reviewed: March 17, 1999