While running some queries on sql server comparing float fields I noticed the following:
For example:
I have a table temp with one column temp_value of type float.
select * from temp returns
2.395
5.678
0.36
1.987
4.65
Now let us say I had two variables:
value_a=0.21
value_b=-0.93
And I ran something like this:
select * from temp where temp_value = ((value_a+value_b)/2)
I would expect the record containing 0.36 to be returned from my query.
But No!!!!!
select * from temp where temp_value = (0.36)
I get the 1 record containing 0.36. Which is what I had expected from the previous query as well.
Lesson Learnt:
Never compare floats without considering the fact that the internal storage of floats might result in data being not comparable unless explicity rounded off before comparison.
