8

In Microsoft SQL Server 2008, I have a table, say myTable, containing about 600k rows (actually, it is a result of joining several other tables, but i suppose this is not important). One of its columns, say value is of type numeric(6,2).

The simple query SELECT value FROM myTable ORDER BY value returns of course about 600k numbers, starting with 1.01 (i.e. the lowest) and ending with 70.00 (highest); no NULLs or other values.

Please notice, that all these values are numeric and positive. However, when calling SELECT LOG(value) FROM myTable, i obtain an error message "An invalid floating point operation occurred".

This error always appears after about 3 minutes of the query running. When copying the 600k values to Excel and counting their LN(), there is absolutely no problem.

I have tried converting value to real or float, which did not help at all. Finally I found a workaround: SELECT LOG(CASE WHEN value>0 THEN value ELSE 1 END) FROM myTable. This works. But why, when all the values are positive? I have tried to take the result and compare the logarithms with those counted by Excel - they are all the same (only differences of the order 10^(-15) or smaller occured in some rows, which is almost surely given by different accuracy). That means that the condition in the CASE statement is always true, I suppose.

Does anyone have any idea why this error occurs? Any help appreciated. Thanks.

Helena
  • 623
  • 1
  • 6
  • 12
  • do any rows contain near zero values (i.e. very small)? – Mitch Wheat May 26 '11 at 09:44
  • Mitch: No, the smallest value is 1.01, which i think is "far enough" from zero... – Helena May 26 '11 at 09:47
  • @Helena: are you 100% positive? If so, why > 0 in the CASE and not > 1 ?? – Mitch Wheat May 26 '11 at 09:48
  • @Mitch: > 1 worked, too. However, > -100 did not. It has something to do with positive numbers, but i wonder why, when all of them are already positive... – Helena May 26 '11 at 09:51
  • @Helena: according to your last comment, they can't all be non-negative....Take out the LOG() and just add a WHERE clause where column < 0: what do you get? – Mitch Wheat May 26 '11 at 09:52
  • @Mitch: An empty resultset :-( – Helena May 26 '11 at 09:57
  • What is even more interesting - now i tried to do `SELECT DISTINCT value INTO tempTable FROM myTable` and then `SELECT LOG(value) FROM tempTable` - and it worked. `tempTable` has about 400 rows. Starts looking like some black magic in SQL Server.... – Helena May 26 '11 at 09:58
  • 1
    @Helena: Might be a bug. It would be nice to reproduce and submit on Connect. – Mitch Wheat May 26 '11 at 10:00
  • 3
    @Helena - It will presumably be doing the `Log` calculation on an invalid value that later gets eliminated by a where clause or join condition. @Mitch there already is one https://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors – Martin Smith May 26 '11 at 10:03
  • 1
    How is your original query organized? It could be possible that LOG() function is applied to some intermediate result set, which may contain not so nice 'bigger than one' numeric values. – Arvo May 26 '11 at 10:04
  • @Martin: that's a very good point. @Helena: can you run the SELECT WHERE < 0 just on the table that holds that column? (I think @Martin has hit the mark) – Mitch Wheat May 26 '11 at 10:05
  • 1
    See this answer for an explanation of a similar issue [TSQL divide by zero encountered despite no columns containing 0](http://stackoverflow.com/questions/5191701/tsql-divide-by-zero-encountered-despite-no-columns-containing-0/5203211#5203211) – Martin Smith May 26 '11 at 10:09
  • 3
    Well, I think Martin and Arvo are right - the table holding the `value` columns surely **does** contain negative values. Only the JOIN and WHERE conditions eliminate them... I just had a wrong idea of how the query is performed. I am almost sure you've got it. Thanks guys! – Helena May 26 '11 at 10:13

2 Answers2

5

You can identify the specific value that's causing the prob;

declare @f numeric(6,2), @r float
begin try select
    @f = value, @r = LOG(value) 
from mytable
end try begin catch
    select error_message(),'value=',@f
end catch
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • 1
    +1. Good idea, but from what Helena posted I fear it may produce nothing! – Mitch Wheat May 26 '11 at 09:59
  • 1
    Thanks for this idea, Alex. It also showed me that there can be a problem with `NULL` values, which arise due to `LEFT JOIN` and are eliminated later in `HAVING` condition (yeah, the query **is** rather complicated). Although `SELECT LOG(NULL)` itself returns `NULL`, not an error, so I am not really sure why it is a problem in a more complex query... – Helena May 26 '11 at 10:19
  • Will @f be assigned if the LOG fails? or will it be for another row that didn't? – gbn May 26 '11 at 10:20
  • I believe so, you would get the correct @f for a table containing say a single negative value (you could also use LOG(@f)) – Alex K. May 26 '11 at 10:23
  • @gbn: Well, i guess @f should be assigned the latest value, independent of whether its LOG() failed or not. Counting LOG(@f) should not change @f, i think. – Helena May 26 '11 at 10:25
3

You would get this error - "An invalid floating point operation occurred" when you do LOG(0). The value of LOG(zero) is indeterminate in the world of Maths, hence the error.

Cheers.

Kwex
  • 3,992
  • 1
  • 35
  • 28