Microsoft KB Archive/183726

= FIX: GROUP BY ALL on a View Shows Columns Not in View =

Article ID: 183726

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q183726



BUG #: 17799 (sqlbug_65)



SYMPTOMS
Transact-SQL provides the ALL keyword in the GROUP BY clause. ALL is meaningful only when the SELECT statement in which it is used also includes a WHERE clause.

If you use ALL, the query results include all groups produced by the GROUP BY clause, even if some of the groups do not have any rows that meet the search conditions. Without ALL, a SELECT statement that includes GROUP BY does not show groups for which no rows qualify.

When using a GROUP BY ALL clause against a view, all of the columns that exist in the underlying table are inadvertently included in the results, even though they are not used in the view.



WORKAROUND
To work around this problem, do one of the following:  Obtain the hotfix mentioned in the STATUS section of this article.

-or- Create a table that does not contain any data, and use that table in one side of the UNION.

-or-  Use statements as in the following example: CREATE VIEW vYes AS     SELECT  fldname FROM    tblYesNo WHERE fldname='YES' UNION SELECT fldname FROM    tblYesNo WHERE fldname='YES' 

NOTE: Using workarounds other than applying the hotfix results in performance degradation.



STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.0.

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

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.



MORE INFORMATION
For more information on the ALL keyword, see the "SELECT Statement" topic in the SQL Server Books Online.

Additional query words: sp sp5security

Keywords: kbbug kbfix KB183726

-

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

© Microsoft Corporation. All rights reserved.