Microsoft KB Archive/255804

= FIX: Incorrect Results with Count Distinct and Aggregate in Select List =

Article ID: 255804

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Service Pack 1

-



This article was previously published under Q255804



BUG #: 57375 (SQLBUG_70)



SYMPTOMS
A query containing all of the following may return incorrect results:
 * An aggregate function in the SELECT list.
 * A COUNT DISTINCT.
 * A join.



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   au_id       au_id --- --- --- ---

238-95-7766 172-32-1176 1          1   267-41-2394 213-46-8915 2           1   274-80-9391 238-95-7766 1           1   409-56-7008 267-41-2394 2           1   427-17-2319 274-80-9391 1           1   472-27-2349 409-56-7008 1           1   486-29-1786 427-17-2319 1           1   648-92-1872 472-27-2349 1           1   672-71-3249 486-29-1786 2           1   712-45-1867 648-92-1872 1           1   722-51-5454 672-71-3249 1           1   724-80-9391 712-45-1867 1           1   756-30-7391 722-51-5454 1           1   807-91-6654 724-80-9391 2           1   846-92-7186 756-30-7391 1           1   899-46-2035 807-91-6654 1           1   998-72-3567 846-92-7186 1           1   998-72-3567 899-46-2035 2           1   998-72-3567 998-72-3567 2           1

Additional query words: DISTINCT aggregate GROUP COUNT

Keywords: kbbug kbfix kbqfe KB255804

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.