SQL Server - Inconsistent, hidden machine precision error when truncating result of LOG to integer

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)

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.

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.

StackExchange Database Administrators Q#209556, answer score: 6