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

bug in string processing if the GO keyword is inside the string

I encoutered a strange behavior using the exec command and I could repreduce
the behavior with the print command:
the command:
print '1
2
3'
is doing it's jub, but if i add go inside the string I get the floowing
error:
print '1
2
go
3'
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '1
2
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1
2
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '3'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '
'.
can someone explain that or point me to a fix I'm using SQL 2000 SP4 on
windows 2003 server with service pack 1
this problem also occurs is the string is sent as a parameter to a stored
procedure using the exec command.
please helpMartin,
This is not a bug. Go is a command that tells SQL Server that this is the
end of a batch of T-SQL statements. If you execute some code in Query
Analyzer and one line has the go word alone, SQL Server will take this as th
e
Go command. For example, try this
print '1
2
go 3
4'
Anyway, I would avoid the go word at the beginning of a line, if possible.
Ben Nevarez, MCDBA, OCP
Database Administrator
"martin" wrote:

> I encoutered a strange behavior using the exec command and I could repredu
ce
> the behavior with the print command:
> the command:
> print '1
> 2
> 3'
> is doing it's jub, but if i add go inside the string I get the floowing
> error:
> print '1
> 2
> go
> 3'
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '3'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '
> '.
>
> can someone explain that or point me to a fix I'm using SQL 2000 SP4 on
> windows 2003 server with service pack 1
> this problem also occurs is the string is sent as a parameter to a stored
> procedure using the exec command.
> please help
>
>|||well, ben,
of course it's a bug
if I get a string as input from a user of a web application, and encode the
string as required like replacing a single apostrophe ' with 2 '' in order
to not break the SQL syntax the same goes with the GO keyword or other
keyword like SELECT.
what exactly should I do in order to pass this kind of a parameter to a
stored procedure? encode it with some way to it's numric ascii
representation?
just image that I will ask you to avoid the END keyword in the beggining of
a sentence in your reply to me, would that not be considered as a bug?
not all string passes as a parameter to a stored procedure are in my control
at all. most of them are not.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:968CB5C7-A808-4767-BAAC-C2F59114E390@.microsoft.com...
> Martin,
> This is not a bug. Go is a command that tells SQL Server that this is the
> end of a batch of T-SQL statements. If you execute some code in Query
> Analyzer and one line has the go word alone, SQL Server will take this as
> the
> Go command. For example, try this
> print '1
> 2
> go 3
> 4'
> Anyway, I would avoid the go word at the beginning of a line, if possible.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "martin" wrote:
>|||martin wrote:
> well, ben,
> of course it's a bug
> if I get a string as input from a user of a web application, and encode th
e
> string as required like replacing a single apostrophe ' with 2 '' in order
> to not break the SQL syntax the same goes with the GO keyword or other
> keyword like SELECT.
>
If you accept string input in that manner and use it for dynamic SQL
then your web application is buggy, dangerous and insecure. This
problem is called SQL Injection and is one important reason why you
should never create dynamic strings out of unverified,
non-parameterized user input. The proper and safe way to do it is to
use parameters in your client code (the ADO parameters collection if
you are using ADO for example).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||martin
> what exactly should I do in order to pass this kind of a parameter to a
> stored procedure? encode it with some way to it's numric ascii
> representation?
It is not a bug, please post exactly what are you doing in order to help
you?
"martin" <news.microsoft.com> wrote in message
news:eAcI3PCbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> well, ben,
> of course it's a bug
> if I get a string as input from a user of a web application, and encode
> the string as required like replacing a single apostrophe ' with 2 '' in
> order to not break the SQL syntax the same goes with the GO keyword or
> other keyword like SELECT.
> what exactly should I do in order to pass this kind of a parameter to a
> stored procedure? encode it with some way to it's numric ascii
> representation?
> just image that I will ask you to avoid the END keyword in the beggining
> of a sentence in your reply to me, would that not be considered as a bug?
> not all string passes as a parameter to a stored procedure are in my
> control at all. most of them are not.
>
>
>
>
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:968CB5C7-A808-4767-BAAC-C2F59114E390@.microsoft.com...
>|||well,
so please explain that:
my app does uses ADO.NET and ado.net succeedes to execute SQL statements
that fails to execute in query analyzer.
is there some magic here?
after executing stored procedure with command object, adding a string
parameter
in the profiler I see the following SQL statement executed from ADO.NET:
declare @.P1 int
set @.P1=33
exec sp_insert_string '748F4655-9106-4D45-A0A2-1AA95C4C8912', 2, N'test',
N'--test
go
-- test
', N'asd', N'James', NULL, @.P1 output
select @.P1
the same stored procedure fails to execute from query analyzer.
I thought the answer will be in some changes to the default behavior of
ADO.NET so I also executed the line performed by ADO.NET to set default
execution variables, with no help:
-- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146383014.820122.76100@.j33g2000cwa.googlegroups.com...
> martin wrote:
> If you accept string input in that manner and use it for dynamic SQL
> then your web application is buggy, dangerous and insecure. This
> problem is called SQL Injection and is one important reason why you
> should never create dynamic strings out of unverified,
> non-parameterized user input. The proper and safe way to do it is to
> use parameters in your client code (the ADO parameters collection if
> you are using ADO for example).
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I've just checked this out on SSMS the SQL 2005 replacement for Query
Analyser.
The problem has been fixed as the print command works perfectly.
The are problems with the Query Analyser SQL interpreter. In a nutshell,
avoid placing Go on it's own at the begginning of the line.
You could change your calling code to do something like
Print '1
2
' + 'Go
3
4'
It's a bodge I know, but it should work ok.
To "Fix" the problem you should upgrade to SQL2005, as I doubt that there
will be another SP for SQL2000.
As mentioned by one of the other posters, you need to make sure that you are
not suseptable to a SQL Injection attack.
OK, you might say that all user input must go through your front end, and
that's 100% secure. But consider the what if scenario.
The simplest way to avoid injection attacks is to use Parameterized queries,
or stored procedures.
The important thing is that any Dynamic SQL uses the sp_executesql command
and uses the parameters properly. i.e. Do not build your query like it's
adhoc sql.
Colin.
"martin" <news.microsoft.com> wrote in message
news:%23GW81yBbGHA.3916@.TK2MSFTNGP03.phx.gbl...
>I encoutered a strange behavior using the exec command and I could
>repreduce the behavior with the print command:
> the command:
> print '1
> 2
> 3'
> is doing it's jub, but if i add go inside the string I get the floowing
> error:
> print '1
> 2
> go
> 3'
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '3'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '
> '.
>
> can someone explain that or point me to a fix I'm using SQL 2000 SP4 on
> windows 2003 server with service pack 1
> this problem also occurs is the string is sent as a parameter to a stored
> procedure using the exec command.
> please help
>|||martin wrote:
> well,
> so please explain that:
> my app does uses ADO.NET and ado.net succeedes to execute SQL statements
> that fails to execute in query analyzer.
> is there some magic here?
> after executing stored procedure with command object, adding a string
> parameter
> in the profiler I see the following SQL statement executed from ADO.NET:
> declare @.P1 int
> set @.P1=33
> exec sp_insert_string '748F4655-9106-4D45-A0A2-1AA95C4C8912', 2, N'test',
> N'--test
> go
> -- test
> ', N'asd', N'James', NULL, @.P1 output
> select @.P1
> the same stored procedure fails to execute from query analyzer.
>
GO is not a T-SQL statement. It is a batch separator used by Query
Analyzer and the other client utilities so this behaviour is correct.
BTW you sould not use the sp_ prefix for user procs. sp_ is reserved
for system procs and may adversely affect performance and reliability
if used in databases other than Master.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ben Nevarez (BenNevarez@.discussions.microsoft.com) writes:
> This is not a bug. Go is a command that tells SQL Server that this is the
> end of a batch of T-SQL statements.
No. GO is just an identifier as far as SQL Server is concerned. That is,
it is not a command or anything.
However, it is a command that is used by many client-tools to signify
the end of batch, that's true.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx