round(round(x,15),15) <> round(x,15)

[Gangolf]

Hi,

I have a strange problem with the round function in T-SQL applied to float
numbers.
This is the code:

declare @x float
set @x = -4.876204545454550e+001
set @x = @x/sqrt(abs(@x))
select convert(char(100),@x,2), convert(char(100),round(@x,15),2),
convert(char(100),round(round(@x,15),15),2) where round(@x,15) <>
round(round(@x,15),15)

Remark: The convert is used only to see all digits on the screen. You can
remove it.

If I round to 14 digits, then all is fine.
How can rounding two times change the number. Using round(x,15) seems to be
critical - the precision of float is only 15 digits, but still - the number
should not be changed then.

There is an even stranger example which you cannot reprocude here for
unknown reasons. I do the same operation but with a number directly from a
table (the number you see above, but NOT copied as string as I did above). In
this case the sign is changed additionally if apply round the second time!!!
I.e. the result of round is -6.982982561523802e+000 but the result of
round(round.. is 6.982982561523802e+000

Thanks, Gangolf

[Alex Kuznetsov]

In general, if you need precision you might want to use decimal which
is precise, not float. Also I had some cases when float numbers
calculations in C# gave me better accuracy than same calculations in T-
SQL. Can you calculate on the client?

[Gangolf]

Unfortunately changing to decimal is not an option.
What I am interested in is if this problem is known and how it is caused
i.e. I want to understand it since I think it is quite scary. A workaround is
to round to 14 digits, which makes more sense anyway but still chosing 15
must not cause a severe (silent) error in the numerics. This is really
dangerous. Almost like the floating point bug in the Intel processors years
ago. I am sure you remember.

Thanks, Gangolf

"Alex Kuznetsov" wrote:

> On Dec 15, 5:37 am, Gangolf <Gang...@discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have a strange problem with the round function in T-SQL applied to float
> > numbers.
> > This is the code:
> >
> > declare @x float
> > set @x = -4.876204545454550e+001
> > set @x = @x/sqrt(abs(@x))
> > select convert(char(100),@x,2), convert(char(100),round(@x,15),2),
> > convert(char(100),round(round(@x,15),15),2) where round(@x,15) <>
> > round(round(@x,15),15)
> >
> > Remark: The convert is used only to see all digits on the screen. You can
> > remove it.
> >
> > If I round to 14 digits, then all is fine.
> > How can rounding two times change the number. Using round(x,15) seems to be
> > critical - the precision of float is only 15 digits, but still - the number
> > should not be changed then.
> >
> > There is an even stranger example which you cannot reprocude here for
> > unknown reasons. I do the same operation but with a number directly from a

> > table (the number you see above, but NOT copied as string as I did above).. In

[--CELKO--]

>>I have a strange problem with the round function in T-SQL applied to FLOAT numbers. <<

Everyone does :) You might want to Google some articles on the IEEE
floating point system. Without going into details, the numbers are in
binary and you display them in decimal and they don't fit well.

As a general statement, DBs prefer to use DECIMAL(s,p) or NUMERIC
(s,p) since they are not subject to that kind of rounding errors you
are having.

[shuu...@gmail.com]

> Unfortunately changing to decimal is not an option.
> What I am interested in is if this problem is known and how it is caused
> i.e. I want to understand it since I think it is quite scary. A workaround is
> to round to 14 digits, which makes more sense anyway but still chosing 15
> must not cause a severe (silent) error in the numerics. This is really
> dangerous. Almost like the floating point bug in the Intel processors years
> ago. I am sure you remember.

Unfortunately, this is the way that floating point works. It is not a
bug. You are trying to express a binary number in a decimal format,
and at best the result is an estimation. When you use ROUND a second
time, you are taking an estimation of an estimation; so the results
can change.

Your options are to either a.) use DECIMAL in your database, or b.)
accept the fact that there will be rounding errors.

[Hugo Kornelis]

On Mon, 15 Dec 2008 03:37:01 -0800, Gangolf wrote:

>Hi,
>
>I have a strange problem with the round function in T-SQL applied to float
>numbers.
>This is the code:
>
>declare @x float
>set @x = -4.876204545454550e+001
>set @x = @x/sqrt(abs(@x))
>select convert(char(100),@x,2), convert(char(100),round(@x,15),2),
>convert(char(100),round(round(@x,15),15),2) where round(@x,15) <>
>round(round(@x,15),15)
>
>Remark: The convert is used only to see all digits on the screen. You can
>remove it.
>
>If I round to 14 digits, then all is fine.
>How can rounding two times change the number. Using round(x,15) seems to be
>critical - the precision of float is only 15 digits, but still - the number
>should not be changed then.

Hi Gangol,

The ROUND function specifies the number of digits behind the decimal
point, whereas the precision of a floating point number is specified in
the number of digits from the first non-zero digit. So 1.23e+010 and
0.000123 both have a precision of 3 digits, but they behave quite
different in ROUND (value, 3).

In your example, there is one digit before the decimal point, so
ROUND(@x,15) actually specifies to round to 16 signigicant digits. Since
floats are accurate up to 15 digits, the differences you see are to be
expected.

>There is an even stranger example which you cannot reprocude here for
>unknown reasons. I do the same operation but with a number directly from a
>table (the number you see above, but NOT copied as string as I did above). In
>this case the sign is changed additionally if apply round the second time!!!
>I.e. the result of round is -6.982982561523802e+000 but the result of
>round(round.. is 6.982982561523802e+000

This, though, is NOT to be expected. Too bad you can't reproduce it.

If you ever see it happening again, then first make sure that neither
the SQL Server code, nor the client-side code is truncating the first
character off of the string rendition of the number. If you can verify
positively that the number really flips its sign, then file a bug. You
can do so on Connect (http://connect.microsoft.com/SQLServer).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Gangolf]

"Hugo Kornelis" wrote:

All right. That is a reasonable answer this time. Thanks.

I have checked the sign flipping once more. It is caused by the convert
function!

Here is an example.
I have a simple table call Numbers with a float column called Val.
CREATE TABLE [dbo].[NUMBERS](
[Item] [int] NOT NULL,
[PointInTime] [int] NOT NULL,
[Id] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Val] [float] NULL
)
Since I only use the Val column you can take any table with a float column.

Here is the code:
SELECT top 1
val as pureval,
convert(binary,val) as binary,
convert(char(100),val,2) val,
convert(char(100),val/sqrt(abs(val)), 2) convertval,
convert(char(100),round(val/sqrt(abs(val)),15), 2) as ConvertRound,
convert(char(100),round(round(sqrt(abs(val)),15),15), 2) as ConvertRoundround,
round(val/sqrt(abs(val)),15) as pureRound,
round(round(val/sqrt(abs(val)),15),15) as pureRoundRound
FROM [NUMBERS]
where val<>0 and round(val/sqrt(abs(val)),15) <>
round(round(val/sqrt(abs(val)),15),15)

The "Top 1" is only for me because I have many numbers in the table.
You can create the table and just copy one number into it. Take it from the
output:

pureval
-48.76204545454550

binary
0x00000000000000000000000000000000000000000000C048618AB498AB50

val
-48.76204545454550

convertval
-6.98298256152380

ConvertRound
-6.98298256152380

ConvertRoundround
6.98298256152380

pureRound
-6.98298256152380

pureRoundRound
-6.98298256152380

As you can see ConvertRoundround flips the sign. If you want to reproduce it
by a code like this:

declare @x float
set @x = -48.76204545454550

set @x = @x/sqrt(abs(@x))
select convert(char(100),@x,2), convert(char(100),round(@x,15),2),
convert(char(100),round(round(@x,15),15),2) where round(@x,15) <>
round(round(@x,15),15)

... the error does NOT appear. So it must be some feature of the number in
the table which is lost when I copy it as decimal string over the clipboard.
That's a pitty.
I have output the binary pattern of the val also just for your information.
If you can make this to a float (which is not possible with explicit cast)
then you can reproduce the error maybe.

Thanks, Gangolf

[Tom Cooper]

"Gangolf" <Gan...@discussions.microsoft.com> wrote in message
news:C22733B4-98CA-4E5D...@microsoft.com...

You have ConvertRoundround computed as:

convert(char(100),round(round(sqrt(abs(val)),15),15), 2) as
ConvertRoundround

of course that is a positive value since you are finding the square root of
the absolute value of val. Which of course returns a nonnegative value.
The other calculations return @val/sqrt(abs(val)), which returns the square
root of abs(val) but preserves the sign of val. That is why that
calculation returns 6.98298256152380 instead of -6.98298256152380.

Tom

[Gangolf]

Ups. Bad mistake. Thanks for the hint.

Then this thread is reduced luckily to the question why for about 30 of 3000
numbers round(round(x,15),15) <> round(x,15).

Thanks, Gangolf