SQL Server Performance Forum – Threads Archive
Function in a group by clause…
i gave an user defined function inside a group by clause and it behaved abnormally returning wrong results. then i had this doubt… whether is it a normal behavior for a group by clause to accept functions? I had a workaround to see the results. in that, my select query returns weird incorrect results and i have attached the complete workaround below. can anyone explain me that weird behavior? create table aa(a int, b int) insert into aaselect a = 1,b = 1
union all
select a = 1,b = 3
union all
select a = 1,b = 4
union all
select a = 2,b = 5
union all
select a = 2,b = 2 create function ab (@a int, @b int)
returns int
as
begin
return (@a – @b)
end select dbo.ab(10,a) A, dbo.ab(10,1) B from aa
group by dbo.ab(10,a)
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
There is no abnormal behaviour. What is your exepcted result? Madhivanan Failing to plan is Planning to fail
Why would you want to do such a simple calculation in a UDF? Especially the second one, which always returns 9 – that’s plain silly. But okay … If the result depends on at least one parameter, then you could use a derived table to have a distinct list of parameters, and you then execute the function for the distinct parameters. select dbo.ab(T.a, Tb)
from (select distinct aa.a, aa.b from aa) T Performance may be identical on small numbers of rows, but it will make a difference once the number of rows increases.
That query which i wrote here was just an accident when i was trying some other thing. I ran the query to see the results weird. just wanna share this. i am not implementing such type of query in my application. And, I am sorry. i gave a wrong query… the correct query is select dbo.ab(1,a) A, dbo.ab(1,1) B from aa
group by dbo.ab(1,a)
try running this query instead. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
I’m not sure you should get any results at all – you should get this error message instead:
GROUP BY expressions must refer to column names that appear in the select list.
I tried with an alias, but it gives similar problems. Use a derived table expression, or use DISTINCT instead of GROUP BY.
Did you read the question I asked you? What is your exepcted result? Madhivanan Failing to plan is Planning to fail
My expected result is … A B
———– ———–
0 0
-1 0 And my actual result is… A B
———– ———–
0 0
-1 -1
Adriaan,
I am not getting any error as you say. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
Then this is enough select distinct dbo.ab1(1,a) A, dbo.ab1(1,1) B from aa
order by 1 desc
Madhivanan Failing to plan is Planning to fail
Hm, yes – if I run your script from the first posting (with some additional GO lines) then there is no error like the one I reported. I was trying to write a query using our own objects, which ran into this problem, but indeed your script doesn’t produce that error. Anyway, the data in the your original script was:
a b
———– ———–
1 1
1 3
1 4
2 5
2 2 When I run your final query against that data, I get these results:
A B
———– ———–
8 0
9 0 The first result column makes sense: the a column in the table is either 1 or 2, so (10 – a) is either 9 or 8. So the grouping in itself is correct. Also, your second result column can only be 0 – your function just subtracts the second function parameter from the first function parameter, so if both parameters have value 1, then the result is always 0. So what is the data from which you expect the results that you listed? I’m getting the impression that you’re not giving us the full complexity here …
… and the only reason why you’re not getting an error is that SQL Server is clever enough to see that your second expression dbo.ab(1,1) returns a single value. Try this: select dbo.ab(10,a) A, dbo.ab(a,b) B from aa
group by dbo.ab(10,a) … it will raise the following errors: Server: Msg 8120, Level 16, State 1, Line 1
Column ‘aa.b’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column ‘aa.a’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Adriaan, when you subtract 1 and 1, you should be getting 0 and you are not getting that 0 when you are doing that subtraction inside a function and use that inside group by clause. It gives us the same results of the column on which group by is applied. I dont know why that happens. i can do that with a distinct clause as madhi said. but, i was curious to know why the correct/expected result is not displayed. for example…
select dbo.ab(1,a) A, dbo.ab(1,1) B from aa
group by dbo.ab(1,a) should display A B
———– ———–
0 0
-1 0 and i am getting A B
———– ———–
0 0
-1 -1
please lemme know if i am confusing still
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
No it correctly display the result. Check that again Madhivanan Failing to plan is Planning to fail
Ram, The expression dbo.ab(1,1) can only return 0, given the definition of the dbo.ab function that you posted. If you’re seeing different results, then the function must be different from what you posted.
I ran it again and i am getting the same results as i got early. i can send you my screenshot if you send me your email ids. Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
No need for separate e-mails – we need sample data, the correct function definition, and the correct query statement.
CREATE FUNCTION ab (@a int, @b int)
RETURNS INT
AS
BEGIN
RETURN (@a – @b)
END SELECT dbo.ab(1,a) A, dbo.ab(1,1) B, dbo.ab(1,1) c FROM aa
GROUP BY dbo.ab(1,a) CREATE TABLE aa(a int, b int) INSERT INTO aa
select a = -1,b = 1
union all
select a = 1,b = 3
union all
select a = 1,b = 4
union all
select a = 2,b = 5
union all
select a = 2,b = 2
union all
select a = 3,b = 9
union all
select a = 3,b = 4
union all
select a = 3,b = 7
Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
This is weird indeed. Try these three queries – funny results … select dbo.ab(a, 2) a, dbo.ab(a, 3) b from aa select dbo.ab(a, 2) a, dbo.ab(a, 3) b from aa
group by dbo.ab(a, 2) select dbo.ab(a, 2) a, dbo.ab(a, 3) b from aa
group by dbo.ab(a, 3) Anyway, seems like you’re breaking some sort of rule.
Apart from that, I still have no idea why you need a GROUP BY clause here … SELECT DISTINCT dbo.ab(1,a) A, dbo.ab(1,1) B, dbo.ab(1,1) c FROM aa
]]>