GROUP BY messes up results of view based on UDF !?!

Hi there,
we have a rather strange effect here were a group by on a view does not
return the expected results. We managed to nail it down to the fact that the
view is based on multiple fields being the result of the same User Defined
Function, but with different arguments. The error seems consistent and in
the example below you can easily see how it fails on the simpelest udf/view
on the pubs db.
Anyone can explain this ' Or better, tell us how to work around it ' (we
may have several situations in our application where this goes wrong, ly
we only just found out)
Thanks.
Cu
Roby
An example : (Pubs db)
DROP FUNCTION dbo.fn_to_upper_or_lower
GO
CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
@.int1 int) -- 0 = UPPER, 1 =
lower
RETURNS varchar(1024)
AS
BEGIN
DECLARE @.result varchar(1024)
IF @.int1 = 0
BEGIN
SELECT @.result = Upper(@.string1)
END
ELSE
BEGIN
SELECT @.result = Lower(@.string1)
END
Return(@.result)
END
GO
-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
-- dbo.fn_to_upper_or_lower('hello', 1)
-- GO
DROP VIEW test
GO
CREATE VIEW test
AS
SELECT title_id,
pub_id,
title,
notes,
upper_case = dbo.fn_to_upper_or_lower (title, 0),
lower_case = dbo.fn_to_upper_or_lower (title, 1)
FROM titles
GO
SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
notes
FROM test
SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
notes
FROM test
GROUP BY title_id, title, upper_case, lower_case, notesLooks like a bug. I reported it and will be back when I have any info.
Tested on SQL2K Dev/SP3.
Bug seems to be fixed in Yukon (tested on CTP2).
BG, SQL Server MVP
www.SolidQualityLearning.com
"deroby" <deroby@.discussions.microsoft.com> wrote in message
news:3C4C4B2B-0646-4D24-9388-F96613CC3A1B@.microsoft.com...
> Hi there,
> we have a rather strange effect here were a group by on a view does not
> return the expected results. We managed to nail it down to the fact that
> the
> view is based on multiple fields being the result of the same User Defined
> Function, but with different arguments. The error seems consistent and in
> the example below you can easily see how it fails on the simpelest
> udf/view
> on the pubs db.
> Anyone can explain this ' Or better, tell us how to work around it ' (we
> may have several situations in our application where this goes wrong,
> ly
> we only just found out)
> Thanks.
> Cu
> Roby
> --
> An example : (Pubs db)
> DROP FUNCTION dbo.fn_to_upper_or_lower
> GO
> CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
> lower
> RETURNS varchar(1024)
> AS
> BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
> END
> GO
> -- SELECT dbo.fn_to_upper_or_lower('hello', 0),
> -- dbo.fn_to_upper_or_lower('hello', 1)
> -- GO
> DROP VIEW test
> GO
> CREATE VIEW test
> AS
> SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
> GO
>
> SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
> notes
> FROM test
> SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
> notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>|||Roby,
This is a known bug. See this thread for details and some suggested
workarounds.
http://groups.google.co.uk/groups?h...&q=kryszak+kass
The bug occurs only in very restricted situations, so a workaround is
usually possible.
Steve Kass
Drew University
deroby wrote:

>Hi there,
>we have a rather strange effect here were a group by on a view does not
>return the expected results. We managed to nail it down to the fact that th
e
>view is based on multiple fields being the result of the same User Defined
>Function, but with different arguments. The error seems consistent and in
>the example below you can easily see how it fails on the simpelest udf/view
>on the pubs db.
>Anyone can explain this ' Or better, tell us how to work around it ' (we
>may have several situations in our application where this goes wrong, ly
>we only just found out)
>Thanks.
>Cu
>Roby
>--
>An example : (Pubs db)
>DROP FUNCTION dbo.fn_to_upper_or_lower
>GO
>CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
>lower
>RETURNS varchar(1024)
>AS
>BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
>END
>GO
>-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
>-- dbo.fn_to_upper_or_lower('hello', 1)
>-- GO
>DROP VIEW test
>GO
>CREATE VIEW test
>AS
>SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
>GO
>
>SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
>SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>
>|||Roby,
Also see http://support.microsoft.com/kb/883415.
SK
deroby wrote:

>Hi there,
>we have a rather strange effect here were a group by on a view does not
>return the expected results. We managed to nail it down to the fact that th
e
>view is based on multiple fields being the result of the same User Defined
>Function, but with different arguments. The error seems consistent and in
>the example below you can easily see how it fails on the simpelest udf/view
>on the pubs db.
>Anyone can explain this ' Or better, tell us how to work around it ' (we
>may have several situations in our application where this goes wrong, ly
>we only just found out)
>Thanks.
>Cu
>Roby
>--
>An example : (Pubs db)
>DROP FUNCTION dbo.fn_to_upper_or_lower
>GO
>CREATE FUNCTION dbo.fn_to_upper_or_lower(@.string1 varchar(1024),
> @.int1 int) -- 0 = UPPER, 1 =
>lower
>RETURNS varchar(1024)
>AS
>BEGIN
> DECLARE @.result varchar(1024)
> IF @.int1 = 0
> BEGIN
> SELECT @.result = Upper(@.string1)
> END
> ELSE
> BEGIN
> SELECT @.result = Lower(@.string1)
> END
> Return(@.result)
>END
>GO
>-- SELECT dbo.fn_to_upper_or_lower('hello', 0),
>-- dbo.fn_to_upper_or_lower('hello', 1)
>-- GO
>DROP VIEW test
>GO
>CREATE VIEW test
>AS
>SELECT title_id,
> pub_id,
> title,
> notes,
> upper_case = dbo.fn_to_upper_or_lower (title, 0),
> lower_case = dbo.fn_to_upper_or_lower (title, 1)
> FROM titles
>GO
>
>SELECT info = 'Without GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
>SELECT info = 'With GROUP BY', title_id, title, upper_case, lower_case,
>notes
> FROM test
> GROUP BY title_id, title, upper_case, lower_case, notes
>
>
>
>|||Thx for the replies guys.
Bit strange to find this has been solved quite a while ago but still is in a
hotfix that only the happy few can get. And then still they make it sound as
if you'd rather prefer not to insall it all...
We'll have a look at the workarounds, they seem do-able, but it sure is a
pain =( I guess optimizers aren't always a developpers best friend...
Cu
Roby
"Steve Kass" wrote:

> Roby,
> Also see http://support.microsoft.com/kb/883415.
> SK
> deroby wrote:
>
>