FIX: Incorrect Results with Count Distinct and Aggregate in Select List (255804)

WORKAROUND

Forcing a different join strategy with a query hint may appear to resolve the problem for a particular query; however, we do not recommend forcing a different join because the problem may occur with any join strategy.

Removing either the COUNT DISTINCT or the aggregate function circumvents the problem.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

For example, the following query returns incorrect results if you execute it against the pubs sample database. The two joined columns in the query return different data when you force a merge join. In this example, if the join is not forced by the query hint, the correct results return.

NOTE: This join is forced to demonstrate the problem against the pubs database; in other examples, the join strategy chosen by the optimizer shows the problem. This problem is not specific to the type of join.

Query

SELECT titleauthor.au_id,authors.au_id , count(authors.au_id),count(distinct authors.au_id)
FROM authors
INNER JOIN titleauthor
ON
authors.au_id = titleauthor.au_id
GROUP BY authors.au_id,titleauthor.au_id
option (merge join)

Results