Coffeehouse Thread

31 posts

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

    Comment removed at user's request.

  • 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
    Deactivated User

    Comment removed at user's request.

  • User profile image
    Deactivated User

    Comment removed at user's request.

  • 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
    Deactivated User

    Comment removed at user's request.

  • 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
    Deactivated User

    Comment removed at user's request.

  • 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
    Deactivated User

    Comment removed at user's request.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.