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

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.

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 t1
GROUP BY year(t1.pubdate)