BUG: SELECT with GROUP BY and WITH CUBE Returns Incorrect Results When You Use a Derived Table (274589)

The information in this article applies to:

Microsoft SQL Server 7.0

This article was previously published under Q274589

BUG #: 58415 (SQLBUG_70)

SYMPTOMS

If you perform a SELECT with a GROUP BY clause and a WITH CUBE clause on a derived table, you may not receive the correct results.

WORKAROUND

Instead of the derived table, use a temporary table as follows:

CREATE TABLE #t(c1 char(1), c2 char(1), c3 char(1), c4 char(1))
INSERT INTO #t VALUES ('a', 'b', 'c', 'd')
GO
SELECT c1, c2, c3, c4
FROM #t
GROUP BY c1, c2, c3, c4
WITH CUBE
GO

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.

MORE INFORMATION

The following query demonstrates the problem:

SELECT c1, c2, c3, c4
FROM (SELECT 'a', 'b', 'c', 'd')
     AS c(c1, c2, c3, c4 )
GROUP BY c1, c2, c3, c4
WITH CUBE

Returns:

(1 row(s) affected)

c1   c2   c3   c4   
---- ---- ---- ---- 
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d
a    b    c    d

(16 row(s) affected)

Modification Type:	Major	Last Reviewed:	10/16/2002
Keywords:	kbBug kbDSupport KB274589