SQL Server - Inconsistent, hidden machine precision error when truncating result of LOG to integer
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.