4

I noticed some strange rounding behavior in SQL Server (2008R2 at least) when casting log base 10 of 1000 to an integer. The answer is clearly the exact value 3, but there must be some decimals SQL server is hiding from the output:

SELECT LOG(1000, 10)--returns 3 with no visible decimals
SELECT CONVERT(INT, LOG(1000, 10))--returns 2

I reverse-engineered the minimum epsilon to get the correct value when added to the result of LOG before conversion as approximately:

SELECT CONVERT(INT, LOG(1000, 10)+0.0000000000000002220446049250313)
SELECT CONVERT(INT, LOG(1000, 10)+0.0000000000000002220446049250312)

enter image description here

It seems I could go on all day making this value more precise.

This only appears to be necessary for the case of 1000, as I get the proper integer values for 1, 10, 100, and 10000 and larger without using any epsilon term.

The error term appears to be the tail of the Taylor Series expanded out 16 terms (as per this page), but why is SQL Server suddenly behaving as expected (no hidden machine precision error) when I try this for the case of 10000 and larger?

Why is SQL Server inconsistently demonstrating a machine precision error?

Thanks.

Improve this question

2 Answers 2

Reset to default
6

The problem is simpler than that. LOG outputs a floating point number, and you should always ROUND when converting form a floating point value to an int instead of truncating.

What you are doing is similar to:

select cast(0.99999999999999999999999999999999999 as int)

The result of LOG(1000,10) is not 3, which you can see

select convert(varchar(200),LOG(1000, 10), 3)

is closest to

   2.9999999999999996e+000

(although internally the significand is stored in base-2).

There's nothing inconsistent about this behavior. LOG is an estimate based on a series expansion that converges to the logarithm, and the result is returned with a limited amount of precision.

The LOG function is designed to return a result accurate to 15 digits of decimal precision, but depending on the arguments the result might be slightly higher or slightly lower than the correct answer.

Improve this answer
4
  • I don't think this addresses the question, but maybe I'm just not making the connection. OP recognizes that there is a precision error, but doesn't understand why that precision error is displayed inconsistently.
    – Rainbolt
    Jun 13, 2018 at 15:43
  • The solution to my particular problem is SELECT CONVERT(INT, ROUND(LOG(n, 10), 15)) (for rounding precision not exceeding 15 because I want the floor of the number) but that was indeed not my question.
    – Elaskanator
    Jun 13, 2018 at 16:06
  • Updated the answer. If that doesn't answer your question, please explain why.
    – David Browne - Microsoft
    Jun 13, 2018 at 16:55
  • I guess what I really should be saying is that whatever ToString method is used to show raw results in the query window is hiding important information. Without a liberal application of ROUND on every single thing how could one realize a value clearly rendered as an integer actually has hidden decimals?
    – Elaskanator
    Aug 3, 2018 at 14:54
1

Why this may be "consistent behavior" for MSSQL, I'd call this one out as being counter-intuitive.
I can't believe I'm saying this, but it looks like SQL Server handles Float as a Larger-Precision Decimal, up until the moment you are done with your Computations, then it Casts as Float to your Variable (or your Select).

MSDN says Log() returns a Float.
What I have observed is Floats are being handled as Larger-Precision Decimal Values:

--Proof:
SELECT CAST(2.99999999999999950000 as Int  )--2
SELECT CAST(2.99999999999999950000 as Float)--3
--Order of Operations Is Observed: Number is Truncated to Int first, then Cast as Decimal.
SELECT CAST(CAST(2.99999999999999950000 as Int  ) as Decimal(30,20))--2.00000000000000000000
--Order of Operations Not-Observed: Number is Never Cast to a Float first before Decimal.
SELECT CAST(CAST(2.99999999999999950000 as Float) as Decimal(30,20))--2.99999999999999950000

--This is the actual value returned.  It has up to 16 Precision,
--   even though a Double-Precision Float has a Max-Precision of 15.
SELECT CAST(LOG(1000, 10) as DECIMAL(30,20))--2.99999999999999950000
--Note: I think the more-accurate answer probably ends in 6, instead of 5.

--To properly Round your value for Integer Expression, I'd use Decimal(10) instead of Int.
SELECT CAST(LOG(1000, 10) as DECIMAL(10))--3
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.