BUG: Internal Error with SELECT Statement Containing Correlated Subquery and GROUP BY

Article translations Article translations
Article ID: 274729 - View products that this article applies to.
This article was previously published under Q274729
BUG #: 58431 (SQLBUG_70)
BUG #: 236396 (SHILOH)
Expand all | Collapse all

SYMPTOMS

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.

WORKAROUND

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
				

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 query reproduces the problem:
SELECT year(pubdate),
 (SELECT SUM(ytd_sales)
  FROM titles t2
  WHERE year(t2.pubdate) = year(t1.pubdate))
FROM titles t1
GROUP BY year(t1.pubdate)
				

Properties

Article ID: 274729 - Last Review: October 16, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbpending KB274729

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com