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?