BUG: Internal Error with SELECT Statement Containing Correlated Subquery and GROUP BY
This article was previously published under Q274729
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 58431 (SQLBUG_70)
BUG #: 236396 (SHILOH)
A query that contains both a correlated subquery and a GROUP BY clause may return the following error message when run against SQL Server 7.0:
Server: Msg 8630, Level 17, State 38, Line 1
Internal Query Processor Error: The query processor encountered an
unexpected error during execution.
When run against SQL Server 2000, it returns the following error message:
Server: Msg 8624, Level 16, State 21, Line 1
Internal SQL Server error.
In both cases, there is no stack dump in the SQL Server errorlog.
To work around this problem, you can use a DISTINCT clause instead of the GROUP BY clause:
SELECT DISTINCT(year(pubdate)), (SELECT SUM(ytd_sales) FROM titles t2 WHERE year(t2.pubdate) = year(t1.pubdate))FROM titles t1
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.
The following query reproduces the problem:
SELECT year(pubdate), (SELECT SUM(ytd_sales) FROM titles t2 WHERE year(t2.pubdate) = year(t1.pubdate))FROM titles t1GROUP BY year(t1.pubdate)
Article ID: 274729 - Last Review: 12/05/2015 21:56:27 - Revision: 3.2
Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition
- kbnosurvey kbarchive kbbug kbpending KB274729