POWER does not reverse LOG and vice versa

user_8249602 0
Viewed 74

The problem I have with POWER/LOG is that although they seem to adapt to the data-type you send in (the output variable type matches the first variable's type), the precision seems to stop close to the limits of a float, resulting in incorrect output. Example:

DECLARE @ten numeric(18,4) = 10
declare @num numeric(18,4) = 234567890
SELECT power(@ten,log(@num,@ten))

Ouput = 234567890.0000 Which is correct

However, if we increase the precision, as follows:

DECLARE @ten numeric(18,6) = 10
declare @num numeric(18,6) = 234567890
SELECT power(@ten,log(@num,@ten))

Output = 234567889.999999 Which is not correct, but rounding could fix it (?)

Lastly, if you change the precision to something like Numeric(18,9), the problem gets worse:

DECLARE @ten numeric(18,9) = 10
declare @num numeric(18,9) = 234567890
SELECT power(@ten,log(@num,@ten))

Output = 234567889.999999310 Which is not correct, and rounding would not fix it.

I'm assuming the issue is that although the POWER and Log function may accept very precise data types, their working variables must be float types? Does anyone have any experience with this, or experience working around it?

math-functions sql-server sql
2 Answers

I think the issue is the return type for LOG is FLOAT, not the dataype you pass in. FLOAT is approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

On the other hand, POWER takes an expression of type float or of a type that can be implicitly converted to float. as input, and returns a type that depends on the input type of the float expression. Meaning, it will return DECIMAL for the input of DECIMAL.

So in your case, LOG is returning a FLOAT that is passed into POWER that returns a FLOAT which, as referenced, isn't precise.

user_61678550

Dealing with non-integer numbers can be handled in two different ways: either using fixed-point arithmetic or floating point arithmetic. Both methods introduce errors.

The log() function is documented as accepting a float as an argument and returning a float. power() behaves a bit differently. The first argument is converted to float, but it also determines the return type. That is why the scale of the return type is driven by the scale of @ten.

All that is happening is that you are seeing the exact same value with different scales. That the number is slightly off is not surprising -- rounding issues are a known problem with non-integer arithmetic on computers.

There is no surprise at all. 234567889.9999993145465850830 is the value being produced. It is the closest that SQL Server can come to the actual answer -- and close enough for most work.

user_11440350
Related