Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: Found bug regarding UDF icw Groupby

    Hi all,

    this is my first posting here, hope you could help me finding a solution for a nasty bug with SQL server.

    My company is a vendor of Business Objects Universes made by us. For a certain application which could run on Oracle or SQL server we have made universes. Lately we have built another version, but we had a showstopper due to a bug in SQL Server.

    Business Objects (=BO) makes the SQL based on the objects you select. A lot of these objects make use of User Defined Objects. With BO you ca easily make reports.

    The following bug is giving us a showstopper:

    Whenever we use a UDF twice with the same column, the result of a groupby-query will fail to show correct information. we use an UDF to get a localized description for a certain code. Imagine we would like to count the codes

    SELECT dbo.udf(TABLE.COLUMN,1033), dbo.udf(TABLE.COLUMN,1043), count(*) from TABLE
    GROUP BY dbo.udf(TABLE.COLUMN,1033), dbo.udf(TABLE.COLUMN,1043)

    What I would expect as result is the following:
    Registered Geregistreerd 4
    Closed Gesloten 23

    What I do get is the following:
    Registered Registered 4
    Closed Closed 23

    With every function I tried the result of the first udf in the groupby will be copied to the result of other results of the same udf.

    If I use another udf with same column, everything is fine
    If I use same udf twice with another columnm, everything is fine
    If I use same udf twice with same column but other non-table or -column related parameters, the results is incorrect.

    I have found a workaround, which won't work in my case though. If I add to every second, third declaration of the UDF in groupby clause " +'' " like:
    SELECT dbo.udf(TABLE.COLUMN,1033), dbo.udf(TABLE.COLUMN,1043) + '', count(*) from TABLE
    GROUP BY dbo.udf(TABLE.COLUMN,1033), dbo.udf(TABLE.COLUMN,1043) + ''

    I tried searching the internet to find similar cases, but I haven't found one.

    Hope someone could help me,

    Sappollo

  2. #2
    Join Date
    Sep 2003
    Posts
    6

    Smile

    BTW, on Oracle everything runs fine

    Sappollo

  3. #3
    Join Date
    Sep 2003
    Posts
    6

    Re: Found bug regarding UDF icw Groupby

    If anyone would try one of the user defined functions.

    I'm using the function formatdate taken from http://www.planet-source-code.com/vb...d=474&lngWId=5

    I have changed this function a little bit, but I think that won't result in my bug...

    Sappollo

  4. #4
    Join Date
    Sep 2003
    Posts
    6
    ...I found something interesting. It seems like SQL Server looks only to the first parameter filled to check the differences between items declared in the group by.

    I discovered this behaviour when I switched the parameters of the function I described before (localized text of a code):

    select dbo.udf(1033,column)
    ,dbo.udf(1034,column)
    , count(*)
    from table
    group by dbo.udf(1033,column),
    dbo.udf(1034,column) ... gave correct results, but now the following query gives wrong results (which worked ok when the parameters were other way around):

    select dbo.udf(1033,column)
    ,dbo.udf(1033,column2)
    , count(*)
    from table
    group by dbo.udf(1033,column),
    dbo.udf(1033,column2)

    Sappollo

  5. #5
    Join Date
    Sep 2003
    Posts
    6

    Thumbs up

    I have found a workable workaround:

    Just enclose isnull(,NULL) :

    select isnull(dbo.udf(1033,column),NULL),
    ,isnull(dbo.udf(1033,column2),NULL)
    , count(*)
    from table
    group by isnull(dbo.udf(1033,column),NULL),
    ,isnull(dbo.udf(1033,column2),NULL)

    Sappollo

  6. #6
    Join Date
    Sep 2003
    Posts
    6

    Thumbs down

    hmm, seems like I'm talking to myself all the time.

    My workaround doesn't seem to work either, grrr.

    The story continues...

    Sappollo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •