Microsoft KB Archive/280468

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

Article ID: 280468

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



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)

Keywords: kbbug kbpending KB280468

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.