1

I am trying to figure out why SQL Server is returning 9.999999999999999e+004 when it's supposed to return 1.000000000000000e+005 from the following sql statement:

select Convert(
               varchar(32), 
               round(cast('123456' as Float), -5),
               2
              )

Even more interesting is that the following statement correctly returns: 1.0000000e+005

select Convert(varchar(32), 
               round(cast('123456' as Float), -5),
               1
              )

Any help would be greatly appreciated.

Sachu
  • 7,555
  • 7
  • 55
  • 94
Radu
  • 11
  • 2

2 Answers2

2

My best guess is that the internal computation for round() is something to the effect:

 (123456 / 100000.0) * 100000.0

The fractional part produced by the division is off by the lowest order bit, as floating point arithmetic is wont to do.

(The above will not reproduce the problem because the computation is between integers and decimals. There are no floating point values.)

Note that you don't need the quotes around '123456' to cause the problem. However, because numbers with a decimal point are interpreted as decimals, rather than floats, it does not happen with convert(varchar(32), 123456.0, 2).

The difference between formats "1" and "2" is interesting. I would put this up to the vagaries of floating point arithmetic as well.

I am guessing that you can figure out pretty easy work-arounds.

And, as I allude to in a comment, this is a bit weird. Floating point representations can exactly represent 123,456 as well as 100,000. The problem must be in an intermediate value.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    Difference between formats `1` and `2` is that the number is converted to `real` (4 bytes) or `float` (8 bytes). Compare output of `cast(cast(100000 as float) as varbinary(32))`, `cast(cast(100000 as real) as varbinary(32))`, `cast(round(cast('123456' as Float), -5) as varbinary(32))` and it becomes obvious what is going on. In essence, `round` returns not exactly `100000`, but when converting this approximation to 4 byte `real` it is rounded further. – Vladimir Baranov Jul 09 '15 at 01:50
-1

sth about how floats cannot represent every single rational number because you're limited to using bits to represent the entire number. 9.999..^4 is the closest the 64-bit or 32-bit float can represent 10^5.

It's not a bug, more like a implementation limitation.

for more info: Wikipedia: Floating Point > Representable Numbers

softwarenewbie7331
  • 967
  • 6
  • 12
  • The problem with this explanation is that SQL Server advertises a float as supporting 7 digits of precision (https://msdn.microsoft.com/en-us/library/ms173773.aspx). And that is sufficient for this representation. – Gordon Linoff Jul 09 '15 at 01:30
  • I might be mistaken but if u take 9.99.e4 to 7 digits of prec it will be ~1.0e5. The 1.0000000e5 in the other result may also be some 0.0000000xxxxx away from the actual ~1.0e5 – softwarenewbie7331 Jul 09 '15 at 01:36
  • 1
    Not true. 10^5 is exactly representable as either a 32-bit or 64-bit IEEE-754 float (as are all integers up to 2^24 and 2^53 respectively). The problem is an intermediate loss of precision, not unrepresentability of the correct answer. – hobbs Jul 09 '15 at 01:44