BUG: Query with Joins, Aggregate, Concatenation May Cause AV (229747)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q229747
BUG #: 55349 (SQLBUG_70)

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 that this is a problem in the Microsoft products that are listed at the beginning of this article.

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)
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbBug kbpending KB229747
©2003 Microsoft Corporation. All rights reserved.