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.

3 answers

  • answered 2018-01-19 05:41 Used_By_Already

    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://docs.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.

  • answered 2018-01-19 08:50 Susang

    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 :)

  • answered 2018-01-19 13:54 Salman A

    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.