Bug with GROUPING keyword?

Hello World,
I have an aggregated query (A) that works fine that uses inner joins.
As soon as the joins are changed to left joins (query B) the following
error occurs:
The grouping aggregate operation cannot take a uniqueidentifier data
type as an argument.
This makes sense b/c a uniqueidentifier is being passed to GROUPING()
but it DOESN'T make sense that I dont get this error from both queries.
Perhaps SQL Server is optimizing it out of the query?
In an attempt to work around this, I've modified all references to the
uniqueidentifier to perform a cast to varchar(36) (see query C). This
seems reasonable given that this same technique allows you to use other
aggregate functions such as count() with uniqueidentifier columns. SQL
Server returns a different error however:
A grouping function argument does not match any of the expressions in
the GROUP BY clause.
I cannot find a reasonable explaination for this error since the
expressions are indeed in the GROUP BY clause.
The 3 queries are below. Please note that they are *generated* by a
report builder application and I'm looking for a generalized fix that
can be made to the sql generator. In general, the queries may have N
number of columns being grouped on so the HAVING statement is a
requirement in order to only include the grand total and details. I've
verified this behavior with SQL 2000 SP3 Standard Edition and Developer
Edition.
Any insights into this behavior and/or suggestions for workarounds are
greatly appreciated!
-- Query A: Works fine
select
(select Code from EnumValue where Id=s.GuidColumn) s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
cast(case when grouping(s.GuidColumn) = 1 then 1 else 0 end as bit)
IsTotalsRow
from
Student s join
(select * from Test where
(AdministrationID='11c68369-7d32-43d3-a132-71a4baf9bbb8')) Test_2002 on
(s.ID = Test_2002.StudentID) join
(select * from Test where
(AdministrationID='f87a92c4-b324-47e4-b8c6-9b53377e8a66')) Test_2003 on
(s.ID = Test_2003.StudentID)
group by
s.GuidColumn
with rollup
having
grouping(s.GuidColumn) in (0, 1)
order by
IsTotalsRow, (select Code from EnumValue where Id=s.GuidColumn) asc
-- Query B: Changed to use LEFT JOIN
-- ERROR:
-- Server: Msg 8161, Level 16, State 1
-- A grouping function argument does not match any of the expressions
in the GROUP BY clause.
select
(select Code from EnumValue where Id=s.GuidColumn) s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
cast(case when grouping(s.GuidColumn) = 1 then 1 else 0 end as bit)
IsTotalsRow
from
Student s LEFT JOIN
(select * from Test where
(AdministrationID='11c68369-7d32-43d3-a132-71a4baf9bbb8')) Test_2002 on
(s.ID = Test_2002.StudentID) LEFT JOIN
(select * from Test where
(AdministrationID='f87a92c4-b324-47e4-b8c6-9b53377e8a66')) Test_2003 on
(s.ID = Test_2003.StudentID)
group by
s.GuidColumn
with rollup
having
grouping(s.GuidColumn) in (0, 1)
order by
IsTotalsRow, (select Code from EnumValue where Id=s.GuidColumn) asc
-- Query C: Changed to use LEFT JOIN and cast uniqueidentifier to
varchar(36)
-- ERROR:
-- Server: Msg 8161, Level 16, State 1
-- A grouping function argument does not match any of the expressions
in the GROUP BY clause.
select
(select Code from EnumValue where Id=cast(s.GuidColumn as
varchar(36))) s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
cast(case when grouping( cast(s.GuidColumn as varchar(36)) ) = 1 then
1 else 0 end as bit) IsTotalsRow
from
Student s LEFT JOIN
(select * from Test where
(AdministrationID='11c68369-7d32-43d3-a132-71a4baf9bbb8')) Test_2002 on
(s.ID = Test_2002.StudentID) LEFT JOIN
(select * from Test where
(AdministrationID='f87a92c4-b324-47e4-b8c6-9b53377e8a66')) Test_2003 on
(s.ID = Test_2003.StudentID)
group by
cast(s.GuidColumn as varchar(36))
with rollup
having
grouping(cast(s.GuidColumn as varchar(36))) in (0, 1)
order by
IsTotalsRow, (select Code from EnumValue where Id=cast(s.GuidColumn as
varchar(36))) asc>> The grouping aggregate operation cannot take a uniqueidentifier data
type as an argument. <<
Since you are using proprietary code, there is no standard or even
reasonable behavior required of them. Without DDL, all we can see is
that you are using the non-relational and expensive uniqueidentifier
data type and have violations of ISO-11179 naming rules.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||Here is the DDL for the 3 tables involved in the queries:
CREATE TABLE EnumValue (
ID uniqueidentifier NOT NULL,
DisplayValue varchar (512) NOT NULL,
Code varchar (8) NULL
)
GO
CREATE TABLE Student (
ID uniqueidentifier NOT NULL,
GuidColumn uniqueidentifier NULL
)
GO
CREATE TABLE Test (
ID uniqueidentifier NOT NULL,
StudentID uniqueidentifier NOT NULL,
AdministrationID uniqueidentifier NOT NULL,
ELALevel uniqueidentifier NULL
)
GO
ALTER TABLE EnumValue ADD
CONSTRAINT PK_EnumValue PRIMARY KEY CLUSTERED (ID)
GO
ALTER TABLE Student ADD
CONSTRAINT DF_Student_Id DEFAULT (newid()) FOR ID,
CONSTRAINT PK_Student PRIMARY KEY CLUSTERED (ID)
GO
ALTER TABLE Test ADD
CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (ID)
GO
ALTER TABLE Test ADD
CONSTRAINT FK_Test_EnumValue FOREIGN KEY (ELALevel) REFERENCES
EnumValue (ID),
CONSTRAINT FK_Test_Student FOREIGN KEY (StudentID) REFERENCES Student
(ID)
GO
--CELKO-- wrote:
> type as an argument. <<
> Since you are using proprietary code, there is no standard or even
> reasonable behavior required of them. Without DDL, all we can see is
> that you are using the non-relational and expensive uniqueidentifier
> data type and have violations of ISO-11179 naming rules.
>
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>>> Since you are using proprietary code, there is no standard or even
Rubbish, Books online contains the product definition and behaviour
definitions.
Try reading it.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:ejOxnTtFGHA.1676@.TK2MSFTNGP09.phx.gbl...
> type as an argument. <<
> Since you are using proprietary code, there is no standard or even
> reasonable behavior required of them. Without DDL, all we can see is
> that you are using the non-relational and expensive uniqueidentifier
> data type and have violations of ISO-11179 naming rules.
>
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Which release and which version of the proprietary feature? They do
change, often. See BIT, *=, ALL() and so forth. Tony, do you put in
a comment that " this proprietary code worked for Foobar_SQL, release
12.3 and might now work in later or prior releases" like you shoudl in
your code?|||After speaking with Microsoft, one workaround they identified is to
move the CAST operation in query C into a view and then reference the
cast'ed column:
create view Stu (ID, GuidColumn) as
(select ID, cast(GuidColumn as varchar(36)) from Student)
Its a bit odd to me that moving this expression into a view is works
but having it inline doesn't!
Also, on a side note, moving the CAST into a UDF does NOT solve the
problem:
CREATE FUNCTION dbo.GuidToStr(@.id uniqueidentifier)
RETURNS varchar(36)
AS
BEGIN
return cast(@.id as varchar(36))
END|||There is no need, its all documented in books online, and if you had
bothered to check and research it then you would have also have found that
too.
Even the bits that are depreciated are documented so when you do your
upgrade you know to check, if you are bothered, check out the SQL Server
2005 mgiration tool that does all this for you.
Are you trying to tell me that between ansi 92 and 99 and 2003 there are no
depreciated items? Well, wake up - there are. Only, where is that
documented? Are there tools to show you the differences? Where you warned in
ansi 92 that something was going to be depreciated in ansi 2003 - Microsoft
has been telling us for 3 releases that the *= stuff is going.
Its about time you actually started reading product documentation before you
post here.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137016404.040788.127160@.g43g2000cwa.googlegroups.com...
> Which release and which version of the proprietary feature? They do
> change, often. See BIT, *=, ALL() and so forth. Tony, do you put in
> a comment that " this proprietary code worked for Foobar_SQL, release
> 12.3 and might now work in later or prior releases" like you shoudl in
> your code?
>|||The final workaround was to restructure the query to union in the
totals row which removes the GROUPING() operator and WITH ROLLUP from
it. This also prevents you from having to create special views with
the casts in them as described in the prior workaround.
Example:
select
(select Code from EnumValue where Id=s.GuidColumn)
s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
0 IsTotalsRow
from
Student s LEFT JOIN
(select * from Test where
(AdministrationID='11c68369-7d32-43d3-a132-71a4baf9bbb8')) Test_2002 on
(s.ID = Test_2002.StudentID) LEFT JOIN
(select * from Test where
(AdministrationID='f87a92c4-b324-47e4-b8c6-9b53377e8a66')) Test_2003 on
(s.ID = Test_2003.StudentID)
group by
s.GuidColumn
UNION ALL
select
null s_GuidColumn,
count(cast(Test_2002.ELALevel as varchar(36)))
Test_2002_ELAELAScoresLevel_Count,
count(cast(Test_2003.ELALevel as varchar(36)))
Test_2003_ELAELAScoresLevel_Count,
1 IsTotalsRow
order by
IsTotalsRow, (select Code from EnumValue where Id=s.GuidColumn)
asc