SQL Server Performance

Function in a group by clause...

Discussion in 'General Developer Questions' started by ramkumar.mu, Mar 29, 2006.

  1. ramkumar.mu New Member

    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 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

    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..."
  2. Madhivanan Moderator

    There is no abnormal behaviour. What is your exepcted result?

    Madhivanan

    Failing to plan is Planning to fail
  3. Adriaan New Member

    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.
  4. ramkumar.mu New Member

    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..."
  5. Adriaan New Member

    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.
  6. Madhivanan Moderator

    Did you read the question I asked you?

    What is your exepcted result?

    Madhivanan

    Failing to plan is Planning to fail
  7. ramkumar.mu New Member

    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..."
  8. Madhivanan Moderator

    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
  9. Adriaan New Member

    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 ...
  10. Adriaan New Member

    ... 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.
  11. ramkumar.mu New Member

    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..."
  12. Madhivanan Moderator

    No it correctly display the result. Check that again

    Madhivanan

    Failing to plan is Planning to fail
  13. Adriaan New Member

    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.
  14. ramkumar.mu New Member

    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..."
  15. Adriaan New Member

    No need for separate e-mails - we need sample data, the correct function definition, and the correct query statement.
  16. ramkumar.mu New Member

    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..."
  17. Adriaan New Member

    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

Share This Page