SQL Float Operation with Round() fails completely

[Peter Scheurer]

Hi,

we found some strange behavior when operating with floats and round().
The following simplified statement reproduces the problem.

select 6.56 - round(convert(f loat, 6.56), 2)
from sysusers where name = 'public';
===========
-8.88178419

In "real world", the floats are not literals, but float fields in tables.
I could figure, that 6.56 - 6.56 will result in something like 0.00001,
but -8.88178419 is beyond my imagination.
The same statement without round results in 0.

Has anyone any clue on this?
Regards,
Peter

[Aaron W. West]

Somehow you're truncating the result, and losing the E-16 (which means
*10^-16). Where are you retrieving your results? I doubt query analyzer
would do this..

select 6.56 - round(convert(f loat, 6.56), 2)

-----------------------------------------------------
-8.8817841970012 523E-16

BTW, you can add E or E0 to the end instead of convert(float, constant)

select 6.56 - round(6.56e, 2)

-----------------------------------------------------
-8.8817841970012 523E-16

6.56 is a decimal constant, and 6.56e is a float. Rounding apparently rounds
up, even when the result is further from the exact number.

select 6.56e union all
select round(6.56e,2)

-----------------------------------------------------
6.5599999999999 996
6.5600000000000 005

If your client app can't deal with floats properly, perhaps you could pass
back a varchar:

select cast(cast(6.56e as decimal(9,2)) as varchar(10))

----------
6.56

Actually, the cast as decimal shouldn't be necessary; casting as varchar
automatically rounds:

select cast(6.56e as varchar(10))

----------
6.56

Actually, it rounds a lot more than I would have thought

select cast(6.560001e as varchar(54))

------------------------------------------------------
6.56

If I want to see more digits, it seems I have to cast as decimal...

select cast(cast(6.560 00001e as decimal(9,8)) as varchar(10))

----------
6.56000001

But be careful:

select cast(cast(6.560 00001e as decimal(9,8)) as varchar(9))
Server: Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.

Return no more than 16 digits (15 decimals) for a number 0..10 and it should
be rounded properly.

select cast(cast(6.56e as decimal(18,17)) as varchar(19))

-------------------
6.5599999999999 9960

select cast(cast(6.56e as decimal(18,15)) as varchar(19))

-------------------
6.5600000000000 00

--

Incidentally, I wonder why QA sees the need to return a 53-character result
for a float. It should never need to be over 18 decimals + 1 for the decimal
point + 5 for the exponent = 24.

Hmm, BOL says this about float data type:

The Microsoft® SQL ServerT float[(n)] data type conforms to the SQL-92
standard for all values of n from 1 to 53. The synonym for double precision
is float(53).

So it's treating the precision (53) as the the width... weird.

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

"Peter Scheurer" <sc******@gmx.c om> wrote in message
news:2b******** *************** ***@posting.goo gle.com...
Hi,

we found some strange behavior when operating with floats and round().
The following simplified statement reproduces the problem.

select 6.56 - round(convert(f loat, 6.56), 2)
from sysusers where name = 'public';
===========
-8.88178419

In "real world", the floats are not literals, but float fields in tables.
I could figure, that 6.56 - 6.56 will result in something like 0.00001,
but -8.88178419 is beyond my imagination.
The same statement without round results in 0.

Has anyone any clue on this?
Regards,
Peter

[Simon Hayes]

"Peter Scheurer" <sc******@gmx.c om> wrote in message
news:2b******** *************** ***@posting.goo gle.com...

Hi,

we found some strange behavior when operating with floats and round().
The following simplified statement reproduces the problem.

select 6.56 - round(convert(f loat, 6.56), 2)
from sysusers where name = 'public';
===========
-8.88178419

In "real world", the floats are not literals, but float fields in tables.
I could figure, that 6.56 - 6.56 will result in something like 0.00001,
but -8.88178419 is beyond my imagination.
The same statement without round results in 0.

Has anyone any clue on this?
Regards,
Peter

The result of the query above is this (on my system):

-8.8817841970012 523E-16

Note the E-16 at the end - that's the number before the E multiplied by 10
to the power of minus 16, which is a very small number indeed. Since float
calculations are not precise, a very small difference is not unusual.
Compare the result of this query:

select cast(1.01 as float)-cast(1.0 as float)

1.0000000000000 009E-2

This is roughly 1E-2, ie. 0.01.

Simon

[Aaron W. West]

I'd like to add that decimal calculations aren't precise either, if you are
calculating with numbers that can't be represented 100% accurately in a
decimal form, eg 1.0/3.0 or 1.0/7.0. Binary calculations are precise on
numbers that are representable accurately in binary in the number of bits
provided in the mantissa of the FLOAT (53 bits) or REAL (24 bits), eg. any
number which can be represented as SUM(POWER(2,x)) where x is a range of
integers such that MAX(x)-MIN(x) <= 52, for FLOAT. Decimals are often not
accurately represented in binary (FLOAT/REAL), because 1/5 is not accurately
representable in binary. The Decimal datatypes change the exponent to a
power of 10, and make the mantissa an integer of 4, 8, 12 or 16 bytes, so
that all decimal numbers of up to 38 digits can be accurately represented.

SELECT 1.-2./3.-1./3.

-----------
.000001

(wow, only 6 decimals by default for a decimal?)

SELECT 1 - 2/3e0 - 1/3e0

-----------------------------------------------------
5.5511151231257 827E-17
SELECT 1.0/7.0*7.0

-------------
.9999990

--SELECT 1.0/7.0*7.0

declare @decimals int
declare @sql varchar(80)
set @decimals=0
while @decimals<37
begin
set @sql='print cast(1.'+replic ate('0',@decima ls)+' as varbinary)'
exec(@sql)
set @decimals = @decimals + 1
end

Some output:
0x0100000101000 000 -- 1. is represented as 1 * 10^(1-1)
0x020100010A000 000 -- 1.0 is 10 (0x0A) * 10^(2-1)
0x0302000164000 000 -- 1.00 is 100 (0x64) * 10^(3-1)... etc
....
0x0B0A000100E40 B5402000000
....
0x1514000100001 0632D5EC76B0500 0000
....
0x1E1D000100000 0A0CA17726DAE0F 1E4301000000
....
0x2524000100000 000109F4BB31507 C97BCE97C000
"Simon Hayes" <sq*@hayes.ch > wrote in message
news:40******** @news.bluewin.c h...

[...] Since float
calculations are not precise, a very small difference is not unusual.
Compare the result of this query:

select cast(1.01 as float)-cast(1.0 as float)

1.0000000000000 009E-2

This is roughly 1E-2, ie. 0.01.

Simon

[Peter Scheurer]

Thank you Simon and Aaron,

you were both right:
I tried the statement with query analyzer and I got the correct and
complete result.
It is sqltalk - a tool from Centura Developer, that does the wrong job
when converting floats for viewing in the result window.

Thank you for helping me.
Peter

The result of the query above is this (on my system):

-8.8817841970012 523E-16

Note the E-16 at the end - that's the number before the E multiplied by 10
to the power of minus 16, which is a very small number indeed. Since float
calculations are not precise, a very small difference is not unusual.
Compare the result of this query:

select cast(1.01 as float)-cast(1.0 as float)

1.0000000000000 009E-2

This is roughly 1E-2, ie. 0.01.

Simon

[Peter Scheurer]

Thank you Simon and Aaron,

you were both right:
I tried the statement with query analyzer and I got the correct and
complete result.
It is sqltalk - a tool from Centura Developer, that does the wrong job
when converting floats for viewing in the result window.

Thank you for helping me.
Peter

The result of the query above is this (on my system):

-8.8817841970012 523E-16

Note the E-16 at the end - that's the number before the E multiplied by 10
to the power of minus 16, which is a very small number indeed. Since float
calculations are not precise, a very small difference is not unusual.
Compare the result of this query:

select cast(1.01 as float)-cast(1.0 as float)

1.0000000000000 009E-2

This is roughly 1E-2, ie. 0.01.

Simon