Coffeehouse Thread

31 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Is there a nice easy way to lodge bug reports?

Back to Forum: Coffeehouse
  • User profile image
    wbarthol

    I need to lodge a bug report for SQL Server (and yes I'm sure it's a bug), what is the best avenue to do this?

  • User profile image
    spod

    wbarthol wrote:
    I need to lodge a bug report for SQL Server (and yes I'm sure it's a bug), what is the best avenue to do this?


    Well, i'm not sure about the best route...but you could tell us here, and i can forward it on. ( i'm interested anyway... )

  • User profile image
    wbarthol

    I've tested this extensively in SQL Server 2003 SP3a, if a computed column contains any money constant you get garbage values in the computed column, here is a sample script:

    DECLARE @tblTest table
        (
        ColA money NOT NULL,
        ColB AS ( $1 + $2 ),
        ColC AS ( [ColA] + $1 )
        )

    INSERT @tblTest
        (
        ColA
        )
    VALUES
        (
        $3
        )

    SELECT
        *
    FROM
        @tblTest


    It doesn't matter if the computed column is based on another column or not, if there is any money constant in it the column's output is corrupted in some way. I've tested on real tables and table variables and the result is equivalent on both. If you change the money constants to numeric ones, the computed columns work fine (but are of course numeric rather than money), here is a script demonstrating that:

    DECLARE @tblTest table
        (
        ColA money NOT NULL,
        ColB AS ( 1.0000 + 2.0000 ),
        ColC AS ( [ColA] + 1.0000 )
        )

    INSERT
        @tblTest
        (
        ColA
        )
    VALUES
        (
        $3
        )

    SELECT
        *
    FROM
        @tblTest

  • User profile image
    gmiley

    Try this:

    DECLARE @tblTest table
        (
        ColA money NOT NULL,
        ColB AS CONVERT(MONEY, ($1 + $2)),
        ColC AS CONVERT(MONEY, ([ColA] + $1))
        )

    INSERT @tblTest
        (
        ColA
        )
    VALUES
        (
        $3
        )

    SELECT
        *
    FROM
        @tblTest


    I have no explanation for it right now, other than ColB and ColC are not acting as money types, regardless that the two items adding together are both money.

  • User profile image
    spod

    This is very strange, and very possibly a bug.

    I first thought it was implicit promotions changing the data types, but the following shows all three are money columns...

    CREATE  table tblTest
        (
        ColA money NOT NULL,
        ColB AS ( $1 + $2 ),
        ColC AS ( [ColA] + $1 )
        )
    go
    sp_help tblTest
    go
    drop table tblTest
    go


    and the above table gives the same broken behaviour.

    Making the column declarations explicit fixes things:

    DECLARE @tblTest table
        (
        ColA money NOT NULL,
        ColB AS CAST (( $1 + $2 ) AS MONEY ),
        ColC AS CAST( ( [ColA] + $1 ) AS MONEY )
        )

    I'll ask the sql guys and see if someone can explain the behaviour...

  • User profile image
    wbarthol

    Thanks, it's not causing me major issues as there are a number of ways around it but since I was pretty sure it was a bug I thought I should report it.

  • User profile image
    pierlove

    Gentlemen,

    I am not sure why just yet but this may help someone track this down.

    Dividing a money type by 1.00000000 (without cast) results in accurate values.

    This may have something to do with the precision of money types.  But I cannot say for sure.

    DECLARE @tblTest table
        (
        ColA money NOT NULL,
        ColB AS ($1)+($3)/(1.00000000),
        ColC AS ( [ColA] + $1 /(1.00000000))
        )

    INSERT @tblTest
        (
        ColA
        )
    VALUES
        (
        $3
        )

    SELECT
        *
    FROM
        @tblTest

    Hmmm????

    Regards,

    Jamie

  • User profile image
    wbarthol

    I believe if you divide by 1.00000000 it will implicitly convert the money variable to numeric and therefore the data type of the computed column will be numeric not money which is why the bug doesn't manifest itself.

  • User profile image
    pierlove

    You are correct.  -JB.   $_

    Doh.

  • User profile image
    pierlove

    wbarthol wrote:
    I believe if you divide by 1.00000000 it will implicitly convert the money variable to numeric and therefore the data type of the computed column will be numeric not money which is why the bug doesn't manifest itself.


    I am curious why doesn't division by /1 produce the same result as /1.00000000 ??

    DECLARE @tblTest table
        (
        ColA money NOT NULL,
        ColB AS ($1 + $3),
        ColC AS ( [ColA] + $1 /1)
        )

    INSERT @tblTest
        (
        ColA
        )
    VALUES
        (
        $3
        )

    SELECT
        *
    FROM
        @tblTest
    go

    Create Table tblTest
        (
        ColA money NOT NULL,
        ColB AS ($1 + $3),
        ColC AS ( [ColA] + $1 /1)
       )
    go
    sp_help tblTest
    go
    drop table tblTest
    go

    Notice the datatype remains money...    Curious..

  • User profile image
    wbarthol

    As far as constants go these are their data types:

    1 - int
    $1 - money
    1.000000 - numeric

    If you divide by 1 which is an int, it uses the higher resolution type which is money, if you divide by 1.000000, the higher resolution type is numeric so this is used instead.

  • User profile image
    pierlove

    Yep. Thanks.

  • User profile image
    spod

    Hi wbarthol

    Thanks for letting us know about this problem.

    we've confirmed this is a bug in sql2000, and have opened a bug against it. It repros on all builds of sql2000 - it doesn't repro against current yukon builds ( haven't tried the beta )...

    You have workarounds so it's not blocking u, is that right?


  • User profile image
    gmiley

    There are ways to get around it so it shouldn't be much of a problem. Just wrap it in a CONVERT/CAST.

    DECLARE @tmpTable TABLE
    (
        myID INT NOT NULL,
        myCash AS (CONVERT(MONEY, $1) + CONVERT(MONEY, $2))
    )
    INSERT @tmpTable VALUES(0)
    SELECT * FROM @tmpTable

    or wrap the entire expression

    myCash AS (CONVERT(MONEY, $1 + $2))


    Which should probably be done, regardless, as a precaution.

  • User profile image
    gmiley

    Ok, this I think will be my last post on this thread for today.

    I did MORE testing and started seeing a pattern.

    turns out that each $1.00 was being converted to '4294967296.0000'. That happens to be 2^32 which is the storage of a signed int. For some reason when $1 is being added to $0, or any $value to any other $VAL I would guess that it is freaking out, for lack of better words, and multiplying ($VAL * 2^32).

    i.e.
    ----------------------------

    DECLARE @tmpTable2 TABLE
    (
        myID INT NOT NULL,
        myCash AS ($0 + $0),
        myCash1 AS ($0 + $1)
    )

    would be

    DECLARE @tmpTable2 TABLE
    (
        myID INT NOT NULL,
        myCash AS (0 + 0)2^32,
        myCash1 AS (0 + 1)2^32
    )


    ----------------------------

    I would assume that to be the case, however when using $0, you get the value: 1704.0645 which, when subtracted the converted myCash1 (4294969000.0901) ends up with 4294967296.0256 instead of 4294967296.

    Maybe this is of some help to someone, or maybe at this point I am just beating a dead horse. =)

  • User profile image
    pierlove

    wbarthol wrote:
    I need to lodge a bug report for SQL Server (and yes I'm sure it's a bug), what is the best avenue to do this?


    I think this is an important question that deserves an answer.  Is there an official "bug report" procedure?


    I mean other than the embedded "a fatal error has occurred at OE, blah, blah, blah.... would you like to send this fatal information to Microsoft? .... that we get when an application crashes for one reason or another (usually because of user impatience, if you ask me, hurry up computer! I clicked it 5 times already!)

    So anyway, I think that it would be good to find out what the official answer to this question is.

    TIA, Jamie.

  • User profile image
    Charles

    For now, feel free to post your bugs here and we will see to it that the appropriate product teams receive the information. Or, even better, a member of the relevant product team will read your post and pass on the information to his/her team. Hey, this might even be the developer who owns the code with the bug. 

    The number of Channel 9 members who are Microsoft employees is growing every day Smiley



    Charles

  • User profile image
    pierlove

    Charles wrote:

    The number of Channel 9 members who are Microsoft employees is growing every day Smiley
    Charles


    Charles,

    That's a great concept that the specific developer that would own a codebase would interact directly via C9 with the UserBase.  That could go far IMHO/.

    Lastly, do you think a public counter that publishes the current number of "Active Microsoft Employee Members" would be in order?

    By active I mean, contributing members.   Are they all part of "The Crew" by default if they are MS Employees?

    Regards,

    Jamie

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.