4

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?

Keith Zink
  • 41
  • 3
  • Do you have data that a) is precise in these ranges and b) where the difference matters? If so, you're in a very rare group and using general purpose constructs for it seems like the opposite of what you'd want. – Damien_The_Unbeliever Jan 03 '19 at 19:27
  • I'm actually storing the log of varying precision, and I need to reverse the log to some fixed precision that would yield an accurate answer. In the examples above (with a singular data set of 234567890), it appears that a precision of four would be the maximum allowed. If i wanted more, it would not be possible to get the original value back. – Keith Zink Jan 03 '19 at 19:35
  • You should consider your input data more carefully and clamp your outputs appropriately. It's unlikely you have even 9 digits of precision in your measurements even before this log/power work. – Damien_The_Unbeliever Jan 03 '19 at 19:39
  • Yes, that was my guess also, which is why I'm looking for alternate methods than POWER/LOG. Doesn't look to have a quick solution. – Keith Zink Jan 03 '19 at 19:41
  • 1
    `LOG` can easily be presented with simple integer arguments that have repeated decimal representations in the decimal type. You can't perform log/power roundtrips without (I believe) switching to systems that use algebra/symbolic logic to represent intermediate states. You certainly won't be able to do it using fixed-space types. – Damien_The_Unbeliever Jan 03 '19 at 19:49
  • 1
    And as an example about precision. If you're measuring length, are you truly claiming that you can measure distance in the up to a 1000km range to *millimeter* precision? Because that's what you're claiming with 9 digits of precision, let alone 18. – Damien_The_Unbeliever Jan 03 '19 at 19:55
  • Well it may be a fool's errand, but ya, I suppose that's what I'm looking for. I'll have to drop a comment if I find a solution. Thanks for your thoughts on the matter, Do you have any reading material on systems that use algebraic/symbolic logic instead of fixed-space types? I suppose that's my next reading assignment. – Keith Zink Jan 03 '19 at 20:03

2 Answers2

3

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.

S3S
  • 24,809
  • 5
  • 26
  • 45
  • 1
    Thanks, I thought I had checked that the output of LOG adapted like POWER, but clearly not. I'm guessing there isn't an easy way to get a log function with a more precise datatype? – Keith Zink Jan 03 '19 at 19:38
  • Newp, and as Damien pointed out, and Gordon, it's difficult for SQL Server to determine what to do... different users would want it handled differently (rounding, truncation, what have you) – S3S Jan 03 '19 at 19:47
3

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.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes, I suppose I was just wondering if anyone has found a solution for a more precise POWER/LOG (though after your answer, it appears I only need a more precise LOG function). Thank you. – Keith Zink Jan 03 '19 at 19:42
  • 2
    @KeithZink You might need a CLR. F.e. like the bounty awarded answer in [this old SO post](https://stackoverflow.com/questions/33938890/). – LukStorms Jan 03 '19 at 20:21
  • Thank you very much, I thought I had searched for something like that, I suppose my next question should be how to search effectively. – Keith Zink Jan 03 '19 at 20:36