BUG: SELECT with GROUP BY ALL Clause in a Nested Correlated Subquery Causes 8624 Error Message or Access Violation (280468)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
This article was previously published under Q280468
BUG #: 57881 (SQLBUG_70)
BUG #: 229658 (SHILOH)

SYMPTOMS

A SELECT statement that contains a GROUP BY ALL clause in a nested correlated subquery may cause the following error in Microsoft SQL Server 2000:
Server: Msg 8624, Level 16, State 24, Line 2
Internal SQL Server error.
In SQL Server 7.0, the query fails with an exception access violation:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 8 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

WORKAROUND

To work around this problem, you can rewrite the query by using temporary tables so that the GROUP BY ALL clause is no longer part of the nested correlated subquery. For example: 
select distinct count (b.title_id) TitleCnt into #MyTmp
from authors a, titleauthor b
where a.au_id = b.au_id
group by all b.au_id

select * from authors where (select TitleCnt from #MyTmp) = 1

STATUS

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

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

MORE INFORMATION

The following code example reproduces the problem: 
use pubs
go

select * from authors a where (select distinct count (title_id) from titleauthor where au_id = a.au_id group by  all au_id ) =1
go
The SQL Server 7.0 error log shows a short stack dump where the beginning of the stack dump looks like the following: 
0x0047f904 Module(sqlservr+7f904) (CCvtTree::PdrgcidGetColumns+c)
0x006a7c97 Module(sqlservr+2a7c97) (CCvtTree::BuildGbAll+5d8)
0x006a64d7 Module(sqlservr+2a64d7) (CCvtTree::CorrelatedAgg+259)
0x00588bf7 Module(sqlservr+188bf7) (CCvtTree::PexprBuildFrom+3d6)
0x004b3645 Module(sqlservr+b3645) (CCvtTree::PexprFromSubquery+34)
0x004546c8 Module(sqlservr+546c8) (CCvtTree::PexprFromScalar+277)
0x00454872 Module(sqlservr+54872) (CCvtTree::PexprFromCompare+17)
0x004545be Module(sqlservr+545be) (CCvtTree::PexprFromScalar+1e5)
0x00454727 Module(sqlservr+54727) (CCvtTree::PexprFromLogical+4f)
0x00454652 Module(sqlservr+54652) (CCvtTree::PexprFromScalar+1fe)
0x00455cad Module(sqlservr+55cad) (CCvtTree::PexprBuildSelect+a7)
0x00446447 Module(sqlservr+46447) (CCvtTree::PexprFromCmdlist+298)
0x00445eba Module(sqlservr+45eba) (CCvtTree::PqryFromTree+2f0)
0x00445c74 Module(sqlservr+45c74) (BuildQueryFromTree+61)
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbBug kbpending KB280468
©2003 Microsoft Corporation. All rights reserved.