BUG: Internal Error with SELECT Statement Containing Correlated Subquery and GROUP BY
This article was previously published under Q274729
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: 10/16/2003 20:39:52 - Revision: 3.2
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition