Math calculation error in T-SQL

[Database Administrator]

Does anyone know why this is happening, or if it is a known bug?

The following select statements is differ only in using "* 0.01" instead of
"/ 100" in the calculation. Mathematically, these are identical. But, the
results are different!

select
CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.01) / 365)) as Wrong,
CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as [Right]

[Database Administrator]

John wrote:
> Does anyone know why this is happening, or if it is a known bug?
>
> The following select statements is differ only in using "* 0.01" instead of
> "/ 100" in the calculation. Mathematically, these are identical. But, the
> results are different!
>
> select
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.01) / 365)) as Wrong,
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as [Right]
>
>
Its rounding


select (10.25 * 0.01)/365
select (10.25 / 100)/365

[Database Administrator]

It seems that you are implicitly losing precision:

select (10.25 * 0.01) / 365
..00028082

select (10.25 * 0.01) / 365.00
..0002808219


Key Point: Always use the explicit degree of precision you require out at a
minimum. I would even put .00 behind the 50 also.


--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.


"John" wrote in message
news:8095787E-12F1-47A0-9B13-AA1801F97AC0@microsoft.com...
> Does anyone know why this is happening, or if it is a known bug?
>
> The following select statements is differ only in using "* 0.01" instead
> of
> "/ 100" in the calculation. Mathematically, these are identical. But,
> the
> results are different!
>
> select
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.01) / 365)) as Wrong,
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as [Right]
>
>

[Database Administrator]

John,

Your question is a reasonable one, but it just technically does not work.
See:

select (10.25 * 0.01) / 365 as Wrong, (10.25 / 100) / 365 as [Right]
Wrong Right
0.00028082 0.0002808219

You can see that it is a matter of precision. You are arriving at your
precisions through implicit conversion. Ideally, you should explicitly
state your precision, either by (a) assigning the data to variable of the
proper type, or (b) CASTing them as NUMERIC or DECIMAL with sufficient
precision for your needs, or (c) typing more decimal places to persuade the
implicit precision. You can see the difference by this version of answer
(c):

select (10.2500 * 0.01) / 365 as Wrong, (10.25 / 100) / 365 as [Right]
Wrong Right
0.0002808219 0.0002808219

Now they are both right. (Or at least they agree.)

I agree that this is often unexpected and is not intuitive, but it is a fact
of life that in math precision matters and implicit conversions may not give
you what you are after.

RLF


"John" wrote in message
news:8095787E-12F1-47A0-9B13-AA1801F97AC0@microsoft.com...
> Does anyone know why this is happening, or if it is a known bug?
>
> The following select statements is differ only in using "* 0.01" instead
> of
> "/ 100" in the calculation. Mathematically, these are identical. But,
> the
> results are different!
>
> select
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.01) / 365)) as Wrong,
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as [Right]
>
>

[Database Administrator]

It's sort of a bug but it's sort of not. Mathematically they are identical
but SQL is not a maths engine and makes assumptions about rounding depending
on the input. Try this:

select CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.010) / 365)) as
NowItsRight

select CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as
[Right]

(10.25 * 0.01) evaluates different to (10.25 * 0.010)

Nigel Ainscoe


"John" wrote in message
news:8095787E-12F1-47A0-9B13-AA1801F97AC0@microsoft.com...
> Does anyone know why this is happening, or if it is a known bug?
>
> The following select statements is differ only in using "* 0.01" instead
> of
> "/ 100" in the calculation. Mathematically, these are identical. But,
> the
> results are different!
>
> select
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.01) / 365)) as Wrong,
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as [Right]
>
>

[Database Administrator]

It all hinges on the precision of intermediate results.

If we execute just the calculations that differ:
SELECT (10.25 * 0.01), (10.25 / 100)

--------- ----------
.1025 .102500

Then take that to the next level:
SELECT .1025 / 365, .102500 / 365

---------- ------------
.00028082 .0002808219

And the next:
SELECT 50 * 7907.91 * .00028082,
50 * 7907.91 * .0002808219

-------------------- ----------------------
111.0349643100 111.035715561450

If you don't want to be at the mercy of the defaults you will have to
control the data type of the base values (using CONVERT).

Roy Harvey
Beacon Falls, CT

On Thu, 1 Nov 2007 08:22:00 -0700, John
wrote:

>Does anyone know why this is happening, or if it is a known bug?
>
>The following select statements is differ only in using "* 0.01" instead of
>"/ 100" in the calculation. Mathematically, these are identical. But, the
>results are different!
>
>select
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.01) / 365)) as Wrong,
> CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as [Right]
>

[Database Administrator]

Several people had the same great answer, that it is all about precision.
While I understand the problem and solution, I still wonder if this is a bug.

Why does SQL Server arbitrarily reduce the precision of a calculation using
0.01 from the precision of a calculation using 100? (You might say there's a
different number of significant digits, but why arbitrarily reduce the
precision below what is reasonably held in the bytes used for the
calculation?)

John


"Roy Harvey (SQL Server MVP)" wrote:

> It all hinges on the precision of intermediate results.
>
> If we execute just the calculations that differ:
> SELECT (10.25 * 0.01), (10.25 / 100)
>
> --------- ----------
> .1025 .102500
>
> Then take that to the next level:
> SELECT .1025 / 365, .102500 / 365
>
> ---------- ------------
> .00028082 .0002808219
>
> And the next:
> SELECT 50 * 7907.91 * .00028082,
> 50 * 7907.91 * .0002808219
>
> -------------------- ----------------------
> 111.0349643100 111.035715561450
>
> If you don't want to be at the mercy of the defaults you will have to
> control the data type of the base values (using CONVERT).
>
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 1 Nov 2007 08:22:00 -0700, John
> wrote:
>
> >Does anyone know why this is happening, or if it is a known bug?
> >
> >The following select statements is differ only in using "* 0.01" instead of
> >"/ 100" in the calculation. Mathematically, these are identical. But, the
> >results are different!
> >
> >select
> > CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.01) / 365)) as Wrong,
> > CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as [Right]
> >
>

[Database Administrator]

On Thu, 1 Nov 2007 09:27:03 -0700, John
wrote:

>Why does SQL Server arbitrarily reduce the precision of a calculation using
>0.01 from the precision of a calculation using 100? (You might say there's a
>different number of significant digits, but why arbitrarily reduce the
>precision below what is reasonably held in the bytes used for the
>calculation?)
>
>John

It has to use SOME basis for determining the precision. Consider:

SELECT .1025 / 365,
.102500 / 365,
.10250000 / 365,
.1025000000 / 365,
.102500000000 / 365,
.10250000000000 / 365,
.1025000000000000 / 365

The values returned: .00028082
.0002808219
.000280821917
.00028082191780
.0002808219178082
.000280821917808219
.00028082191780821917

Which one is "right"? There is no specific precision that can't be
made more accurate by adding even more decimal places. Clearly the
line has to be drawn at some point. The choice to base it off the
precision of the input data seems reasonable. Certainly it should not
be less than the input basis. But what rule should be used to make it
longer?

I just don't see it as an issue, but that's just me.

Roy Harvey
Beacon Falls, CT

[Database Administrator]

SQL is working as documented. The rules for the precision and scale of
results of calculations with decimals can be found in BOL (use the index tab
to search for precision) or see the online version of BOL at:
http://msdn2.microsoft.com/en-us/library/ms190476.aspx

For your "wrong calculation" you are multiplying two numbers, the first with
precision 4 and scale 2, the second with precision 2 and scale 2. so the
result has precision 4+2+1 or 7 and scale 2+2 or 4.

For your "right calculation", you are dividing two numbers the first with
precision 4 and scale 2, the second with precision 3 and scale 0 (SQL
converts the int to the smallest scale and precision that will hold the
value 100). So the result of this calculation has precision 4-2+0+max(6,
2+3+1) or 8 and a scale of max(6, 2+3+1) or 6.

So the intermediate result has a different precision and scale, which
affects the results of the whole calculation just enough so that before you
convert the final result to decimal(12,2), the "wrong" one has a value of
111.0349643100 and the "right" one a value of 111.035715561450 and when you
convert them to decimal(12,2), they are rounded to 111.03 and 111.04.

Tom

"John" wrote in message
news:F4B017B6-C2D2-4ACE-BE78-407FB5605175@microsoft.com...
> Several people had the same great answer, that it is all about precision.
> While I understand the problem and solution, I still wonder if this is a
> bug.
>
> Why does SQL Server arbitrarily reduce the precision of a calculation
> using
> 0.01 from the precision of a calculation using 100? (You might say
> there's a
> different number of significant digits, but why arbitrarily reduce the
> precision below what is reasonably held in the bytes used for the
> calculation?)
>
> John
>
>
> "Roy Harvey (SQL Server MVP)" wrote:
>
>> It all hinges on the precision of intermediate results.
>>
>> If we execute just the calculations that differ:
>> SELECT (10.25 * 0.01), (10.25 / 100)
>>
>> --------- ----------
>> .1025 .102500
>>
>> Then take that to the next level:
>> SELECT .1025 / 365, .102500 / 365
>>
>> ---------- ------------
>> .00028082 .0002808219
>>
>> And the next:
>> SELECT 50 * 7907.91 * .00028082,
>> 50 * 7907.91 * .0002808219
>>
>> -------------------- ----------------------
>> 111.0349643100 111.035715561450
>>
>> If you don't want to be at the mercy of the defaults you will have to
>> control the data type of the base values (using CONVERT).
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On Thu, 1 Nov 2007 08:22:00 -0700, John
>> wrote:
>>
>> >Does anyone know why this is happening, or if it is a known bug?
>> >
>> >The following select statements is differ only in using "* 0.01" instead
>> >of
>> >"/ 100" in the calculation. Mathematically, these are identical. But,
>> >the
>> >results are different!
>> >
>> >select
>> > CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.01) / 365)) as
>> > Wrong,
>> > CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as
>> > [Right]
>> >
>>

[Database Administrator]

Thanks Tom,

The link will be useful. I guess I would have just assumed that "0.01" had
a precision of 3, just like 100. Ah well. Live and learn...

John

"Tom Cooper" wrote:

> SQL is working as documented. The rules for the precision and scale of
> results of calculations with decimals can be found in BOL (use the index tab
> to search for precision) or see the online version of BOL at:
> http://msdn2.microsoft.com/en-us/library/ms190476.aspx
>
> For your "wrong calculation" you are multiplying two numbers, the first with
> precision 4 and scale 2, the second with precision 2 and scale 2. so the
> result has precision 4+2+1 or 7 and scale 2+2 or 4.
>
> For your "right calculation", you are dividing two numbers the first with
> precision 4 and scale 2, the second with precision 3 and scale 0 (SQL
> converts the int to the smallest scale and precision that will hold the
> value 100). So the result of this calculation has precision 4-2+0+max(6,
> 2+3+1) or 8 and a scale of max(6, 2+3+1) or 6.
>
> So the intermediate result has a different precision and scale, which
> affects the results of the whole calculation just enough so that before you
> convert the final result to decimal(12,2), the "wrong" one has a value of
> 111.0349643100 and the "right" one a value of 111.035715561450 and when you
> convert them to decimal(12,2), they are rounded to 111.03 and 111.04.
>
> Tom
>
> "John" wrote in message
> news:F4B017B6-C2D2-4ACE-BE78-407FB5605175@microsoft.com...
> > Several people had the same great answer, that it is all about precision.
> > While I understand the problem and solution, I still wonder if this is a
> > bug.
> >
> > Why does SQL Server arbitrarily reduce the precision of a calculation
> > using
> > 0.01 from the precision of a calculation using 100? (You might say
> > there's a
> > different number of significant digits, but why arbitrarily reduce the
> > precision below what is reasonably held in the bytes used for the
> > calculation?)
> >
> > John
> >
> >
> > "Roy Harvey (SQL Server MVP)" wrote:
> >
> >> It all hinges on the precision of intermediate results.
> >>
> >> If we execute just the calculations that differ:
> >> SELECT (10.25 * 0.01), (10.25 / 100)
> >>
> >> --------- ----------
> >> .1025 .102500
> >>
> >> Then take that to the next level:
> >> SELECT .1025 / 365, .102500 / 365
> >>
> >> ---------- ------------
> >> .00028082 .0002808219
> >>
> >> And the next:
> >> SELECT 50 * 7907.91 * .00028082,
> >> 50 * 7907.91 * .0002808219
> >>
> >> -------------------- ----------------------
> >> 111.0349643100 111.035715561450
> >>
> >> If you don't want to be at the mercy of the defaults you will have to
> >> control the data type of the base values (using CONVERT).
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
> >> On Thu, 1 Nov 2007 08:22:00 -0700, John
> >> wrote:
> >>
> >> >Does anyone know why this is happening, or if it is a known bug?
> >> >
> >> >The following select statements is differ only in using "* 0.01" instead
> >> >of
> >> >"/ 100" in the calculation. Mathematically, these are identical. But,
> >> >the
> >> >results are different!
> >> >
> >> >select
> >> > CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 * 0.01) / 365)) as
> >> > Wrong,
> >> > CONVERT(decimal(12, 2), 50 * 7907.91 * ((10.25 / 100) / 365)) as
> >> > [Right]
> >> >
> >>
>
>
>