BUG: Select Query with GROUP BY and Outer Join Causes Handled Access Violation

• Microsoft SQL Server version 7.0

SYMPTOMS

A handled access violation (AV) exception error occurs within a SELECT statement under the following conditions:

• There is an outer join and the outer table has a nonclustered unique index on the column in the JOIN condition.



• The SELECT statement contains a GROUP BY clause with two or more columns.



• A number of the columns from both tables are used in the search conditions of the WHERE clause.



• The execution plan does not cause a sort or ORDER BY.

Short Stack Dump
0x006bdcab Module(sqlservr+2bdcab) (SDES::RepositionScan+39)
0x005911db Module(sqlservr+1911db) (RowsetSS::ReaquireLatchLong+b4)
0x00421775 Module(sqlservr+21775) (RowsetSS::GetData+42)
0x004ac022 Module(sqlservr+ac022) (CValSSTable::GetDataX+1c)
0x00630cc8 Module(sqlservr+230cc8) (CEs::FastCompare+7e)
0x00426876 Module(sqlservr+26876) (CDataBuffer::FGetPredicate+3a)
1999-06-23 12:20:31.87 spid9    CImageHelper::GetSym Error - The specified module could not be found. 

WORKAROUND

The problem occurs when the execution plan performs a Hash Match without any sorting or ordering. The workaround is to apply either query hints or join hints that will create an execution plan that performs a sort or an order.

• Use the 'order group' query hint.
  
  -or-



• Use the 'merge', 'loop', or 'hash' join hint.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words:

Keywords          : kbbug7.00 kbSQLServ700bug 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: July 12, 1999