Issue in floating point comparison leads to incorrect query result in DolphinDB GUI

Question

My environment: DolphinDB server 2.00.8 on Linux

I used a where condition (which compares floating point numbers) to filter table data, but the query result is incorrect.

Here’s what I did:

I have a table containing the result of my factor analysis on level2 market data. See screenshot below:

I want to select data from the table with the condition: factorValue > percentile(factorValue, 80).

The result of percentile(factorValue, 80) is 2679.999999999999545. Therefore, in the screenshot above, there should be 2 records satisfying this condition: the records with factorValue 80329.0602 and 2680.

But the query only returned 1 record. The record with factorValue = 2690 is not returned. Where is the problem?

Answer 1

Execute 2680 > 2679.999999999999545 in DolphinDB GUI. The result is true, which is correct. So the problem is not in the floating point comparison.

The problem may be the precision of numeric values. By default, DolphinDB GUI uses 16 digits of precision. Therefore, if the calculated factorValue is actually 2679.99999, it will be displayed as 2680 in GUI.

Solution:

Adjust the default number of decimal places in GUI. For example, set it to 16. Now let’s check the value of factorValue in higher precision to see if it is actually smaller than 2680. If yes, our assumption is correct.

If you still want to display the factorValue as 2680.0 in the table, use the function round to specify the number of decimal places applied in the comparison. This way you’ll still get the correct query result.

// construct data
HTSCSecurityID = take(`600000.SH, 6)
MDDate = take(2022.04.01, 6)
MDTime = 13:00:00.000 + 1..6 * 3000
factorValue = 0 0 0 80329.0602 751 2679.99999
resultTable = table(HTSCSecurityID, MDDate, MDTime, factorValue)

// filter for records with factorValue greater than 2679.999999999999545
select * from resultTable where round(factorValue, 4) > 2679.999999999999545