BUG: LEFT OUTER JOIN Tables May Cause Handled Access Violation

• Microsoft SQL Server version 6.5

SYMPTOMS

A complex SELECT query that contains multiple LEFT OUTER JOIN tables in the FROM clause may cause a handled access violation (AV). For example, if the tables used in the query are very wide, the following ANSI-style left outer join query may cause handled AV when executed:

   SELECT table_1.col1
   FROM ((((table_1
   LEFT OUTER JOIN table_2 ON table_1.col2=table_2.col1)
   LEFT OUTER JOIN table_3 ON table_2.col1=table_3.col1)
   LEFT OUTER JOIN table_4 ON table_1.col3=table_4.col1)
   LEFT OUTER JOIN table_5 ON table_3.col2=table_5.col1)
   WHERE table_5.col2 = 'somestring'

WORKAROUND

You may be able to work around this problem by using the non-ANSI-style outer joins. For example, instead of using LEFT OUTER JOIN in the FROM clause, you can use the left outer join operator, that is, "*=", in the WHERE clause, and list all the tables involved in the FROM clause.

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.