BUG: Query with Joins, Aggregate, Concatenation May Cause AV

• Microsoft SQL Server version 7.0

SYMPTOMS

An access violation (AV) may occur when executing a query that:

• Contains a nested loops join that scans a table (that is, a naive nested loops join).



• Contains a correlated self-join subquery that computes an aggregate.



• Contains a GROUP BY clause containing a concatenated column.



• Uses the same concatenated column as a join condition for the self-join subquery.

WORKAROUND

To work around this problem, do either of the following:

• Create indexes that can be used by the nested loops join.
  
  -or-



• Compare single columns instead of concatenated columns.

STATUS

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

MORE INFORMATION

The SQL Server error log will contain the following short stack dump:

   0x006a66a9 Module(sqlservr+2a66a9)(SDES::RepositionScan+39)
   0x0056ddde Module(sqlservr+16ddde)(RowsetSS::ReaquireLatchLong+b4)
   0x004897e5 Module(sqlservr+897e5) (RowsetSS::GetData+42)
   0x00490239 Module(sqlservr+90239) (CValSSTable::GetDataWithBuffer+34)
   0x00484b98 Module(sqlservr+84b98) (GetDataWithBuffer+28)
   0x0041be66 Module(sqlservr+1be66) (CEs::GeneralEval+e3)
   0x004895b6 Module(sqlservr+895b6) (CQScanFilter::GetRow+2c)
   0x0048945f Module(sqlservr+8945f) (CQScanStreamAggregate::GetRow+3d)
   0x00641c13 Module(sqlservr+241c13)(CQScanSpool::GetRow+b9)
   0x00489312 Module(sqlservr+89312) (CQScanNLJoin::GetRow+121)
   0x0041d138 Module(sqlservr+1d138) (CQueryScan::GetRow+11)
   0x0041ca1d Module(sqlservr+1ca1d) (CStmtQuery::FExecuteQuery+338)
   0x0041c21b Module(sqlservr+1c21b) (CStmtSelect::XretExecute+19c)
   0x00419efa Module(sqlservr+19efa) (CMsqlExecContext::ExecuteStmts+118)
   0x00419a0f Module(sqlservr+19a0f) (CMsqlExecContext::Execute+16b) 

Additional query words: handled exception errorlog

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

Last Reviewed: May 18, 1999