Is there a nice easy way to lodge bug reports?

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?

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

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

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.

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

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.

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

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.

You are correct.  -JB.   $_

Doh.

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

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.

Yep. Thanks.

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?

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.

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. =)

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.

Charles wrote:

The number of Channel 9 members who are Microsoft employees is growing every day
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