CODEWITHSUNDEEP
sqlsql-serversql-server-2012floating-pointprecision
Shushil Bohara
Shushil Bohara

Reputation: 5656

Why FLOAT type data comparison is not working even providing the exact data

In real table with the same value

SELECT * FROM float_value WHERE val = 49640.2473896214 -- No data returns

If I round it upto the total precision then it works 

SELECT * FROM float_value WHERE ROUND(val, 10) = ROUND(49640.2473896214, 10) --Returning Data

After that I have created temporaty table with the same value 49640.2473896214 and it works in the first query which is failed above

CREATE TABLE #testing(Vvalue FLOAT)
INSERT INTO #testing VALUES (49640.2473896214)

SELECT * FROM #testing WHERE Vvalue = 49640.2473896214 -- Simply returning row

Would you please help me to figure out this why = comparison is not working in the above ? If I should use ROUND always then it would be another problem to figure out the precision to be rounded and compare.

I want the result if we provide input what exactly visible in the field i.e. = 49640.2473896214 should return value.

Thank you in advance.

Upvotes: 6

Views: 4838

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272166

Floating point numbers cannot represent values exactly as you want, for example:

  • 49640.2473896214321 gets stored as 49640.2473896214287378825247287750244140625
  • 49640.2473896214521 gets stored as 49640.247389621450565755367279052734375
  • 17.56 gets stored as... left as exercise

It is also worth noting that the displayed value of floats is usually an approximation. Some environments allow you to change the precision of the displayed value but I could not find any such setting in SQL server. Having said all that:

SELECT * FROM float_value WHERE val = 49640.2473896214 -- No data returns

That is because 49640.2473896214 does not exist in the database. The value in database could be ...62139... or ...62141..., who knows.

Would you please help me to figure out this why = comparision is not working in the above?

It should work if you supply the exact value stored in database (used in previous INSERT or UPDATE operation). If you supply the value you see in the database then see notes above.

If I should use ROUND always then it would be another problem to figure out the precision to be rounded and compare.

ROUNDing returns FLOAT for FLOATs so you could end up with similar issues. The most cited solution for this problem is subtract the two numbers and check if the difference is very small:

select * from #testing WHERE ABS(vvalue - 49640.24738962     ) < 1e-11
-- id | vvalue           | actual_value
-- 1  | 49640.24738962   | 49640.24738962

select * from #testing WHERE ABS(vvalue - 49640.2473896214   ) < 1e-11
-- id | vvalue           | actual_value
-- 2  | 49640.2473896214 | 49640.2473896214

select * from #testing WHERE ABS(vvalue - 49640.2473896214321) < 1e-11
-- id | vvalue           | actual_value
-- 3  | 49640.2473896214 | 49640.2473896214321

select * from #testing WHERE ABS(vvalue - 49640.2473896214521) < 1e-11
-- id | vvalue           | actual_value
-- 4  | 49640.2473896215 | 49640.2473896214521

The 1e-11 is referred to as epsilon, the amount of tolerance you can accept. You can set it to something smaller but not smaller than 1e-16 as far as I can tell.

Upvotes: 5

Shushil Bohara
Shushil Bohara

Reputation: 5656

Now, I understand the scenario of FLOAT field values as its storing upto 10 precision by rounding the given values

For Example:

CREATE TABLE #testing(id INT IDENTITY(1,1), Vvalue FLOAT, actual_value VARCHAR(50))
INSERT INTO #testing VALUES 
(49640.24738962, '49640.24738962'),
(49640.2473896214, '49640.2473896214'),
(49640.2473896214321, '49640.2473896214321'),
(49640.2473896214521, '49640.2473896214521')

value saved as:

id  Vvalue              actual_value
1   49640.24738962      49640.24738962      --Saved same as input
2   49640.2473896214    49640.2473896214    --Saved same as input
3   49640.2473896214    49640.2473896214321 --Saved upto 10 precision only by rounding leaving trailing zeros
4   49640.2473896215    49640.2473896214521 --Saved upto 10 precision only by rounding leaving trailing zeros

Now, Apparently following query should return two rows 2,3 but row 3 value is not exactly as out input

SELECT * FROM #testing WHERE Vvalue = 49640.2473896214 

id  Vvalue              actual_value
2   49640.2473896214    49640.2473896214

In my case, it should return both rows 2,3 so, if I round comparison column value by 10 then it will give what I want and it doesn't matter for me now, what unseen value it's holding ? I just simply want to receive what's present there in the table

SELECT * FROM #testing WHERE ROUND(Vvalue, 10) = 49640.2473896214

id  Vvalue              actual_value
2   49640.2473896214    49640.2473896214
3   49640.2473896214    49640.2473896214321

Thank you everyone for sharing your ideas and boost up my mind :)

Upvotes: 2

Paul Maxwell
Paul Maxwell

Reputation: 35603

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. https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql

This can be very frustrating, but some float values will fail comparisons by the equal operator and it is necessary to fix the decimal precision to enable reliable use of equal.

Upvotes: 4

Related Questions