I am working on a program that needs to work on floating point values that are fetched from different database types: currently we support 12 different DBMS (for example, two of them are Sqlite3 and MariaDB).
My code applies some business logic to the values fetched from the database, a floating point score is calculated and values are ordered by the score. I have written tests for this business logic. Due to the differences in an order of magnitude about of 10^-6 between those values when fetched from different databases, the ordering outcome depend on the database type.
For production, I believe difference in ordering due to a difference of 10^-6 is acceptable. (Especially because we say the data integrity and quality is user's responsibility for our product.) Also, our tests for fetching values from dbs test up to a 10^-5 precision.
What is the most effective way to test this automatically? Namely, ordering changes due to small differences of floating point inaccuracy.
Note. Some clarificaiton as requested
SQL query is aggregation with some group by clauses (different aggregation functions are tested) and two different time period filters. In this example aggregation function was mean of a column. So two dataframes are fetched from the database with same columns.
These two dataframes are joined on group by columns. Difference of metric column for both time periods are calculated.
Score is difference * z-score of score.
The 10^-6 difference is from the way Sqlite3 and MariaDB calculates mean. All databases for each db type is created and inserted at the start of each test using same csv files as input.