BUG: Use of COALESCE Function Against a Subquery with an Aggregate Function May Cause Error 8624 (317527)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)

This article was previously published under Q317527

BUG #: 356377 (SHILOH_BUGS)

SYMPTOMS

If you use the COALESCE function against a subquery that has an aggregate function, you may encounter the following error message in SQL Server 2000:

Server: Msg 8624, Level 16, State 25, Line 1
Internal SQL Server error.

WORKAROUND

To work around the problem, replace the COALESCE function with the CASE function.

For example:

USE pubs
GO
SELECT CASE WHEN((SELECT SUM(ta.au_ord) from titleauthor ta
			WHERE ta.au_id = '172-32-1176' GROUP BY ta.royaltyper) IS NOT NULL)
THEN (SELECT SUM(ta.au_ord) from titleauthor ta
			WHERE ta.au_id = '172-32-1176' GROUP BY ta.royaltyper) 
ELSE 0
END
GO

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

To reproduce the problem, execute the following statements against a SQL Server 2000 server:

USE pubs
GO
SELECT COALESCE((SELECT SUM(ta.au_ord) from titleauthor ta
			WHERE ta.au_id = '172-32-1176' GROUP BY ta.royaltyper) , 0)
GO

You will notice the same 8624 error even if the SUM aggregate function was replaced with another aggregate function.
When run against a SQL Server 7.0 server, the same statements return the expected value of 1.

Modification Type:	Major	Last Reviewed:	10/3/2003
Keywords:	kbbug kbpending KB317527