How to test variable values which differ due to floating point inaccuracy caused by different DBMS types?

Question

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

1. 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.

2. These two dataframes are joined on group by columns. Difference of metric column for both time periods are calculated.

3. Score is difference * z-score of score.

4. 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.

Answer 1

I see the following alternatives:

1. Tailor your test data to avoid the problem (so no two score values differ by a value as small as 10^-6)

2. Write a special comparison function for the tests which tolerates the differences (for this, it will be better to fetch the values from the DB using the highest available precision, not by a cutted precision like 10^-5)

3. Provide different test assessment data for each DBMS (or at least each group of DBMS where you expect differences).

4. Store real numbers not as floating point, but in a database independent format (like a fixed point format, or a string type with a fixed number of decimals), and make sure all calculations are done with exactly the same internal precisions.

If option 1 is acceptable or not is a question of the acceptable overall risk and of the gory details of the business requirement and the scoring. There are systems where this is ok, and others, where it is not.

Option number 2 can be tricky, but that is probably what you would also do in manual testing. It may, however, come with a certain risk of overlooking certain kind of regressions.

Option 3 will require more maintenance effort for the assessment data - if it is manageable depends on the number of tests which would require such differentiated data, and the number of DBMS groups for which different data would become necessary.

Option 4 may be feasible for financial systems, where all the numbers involved may have a defined number of two or three decimals, and systems where the calculations are exclusively done at the client side, and none at the database side.

Answer 2

One approach would be to have two separate tests, one for the score for each result being within an epsilon range around the expected value, the other for correct ordering given the actual scores.

It might still be valuable to analyze the cause of differences, and to choose a database representation which allows you to store and process values exactly the same in the different environments. However, if that's not feasible, I'd go for two separate tests for correctness of scores within tolerance range and correctness of ordering.

Answer 3

I'd suggest a slight variation on what others have suggested:

1. Validate the score and the sort order separately
2. Validate the scores with some epsilon (e.g. score must be correct to 0.00001)
3. Validate that the results order respects score order: ensure each item in the list has a score greater than or equal to (using an epsilon of 0.00001) the next item in the list.

With this approach, you will validate that items are sorted correctly without needing to fallback to dynamic sorting or some pre-determined "expected" sorting.

Answer 4

From your comment reply:

"Are you asking how to test that the order changes between DBMSes?" yes

If that is the kind of test you want to write, then this inherently has to be an integration test, where you take the same data set, store it in different data stores, fetch it back in a sorted fashion, and compare the difference in order.

Something along the lines of:

double[] myData = new[]
{
    1.234567,
    1.234568  // difference of 1 * 10 ^-6
}

myProviderA.Save(myData);
myProviderB.Save(myData);

var resultA = myProviderA.GetSorted(myData);
var resultB = myProviderB.GetSorted(myData);

// Assert the lists are in a DIFFERENT order.

Obviously, your test data should use numbers which you know produce a different sorting. I just picked a random number.

However, I would offer that this is not the most productive way to go about it.

If differences on the order of magnitude of 10^-6 are not a problem for you, then there's no real purpose to writing a test to find out if there is a difference on the order of magnitude of 10^-6.
In other words, there's no point to me looking up Burger King locations if I'm not going to eat Burger King.

Instead, it may be better to focus on making sure that there are no differences on an order of magnitude that does matter to you. For the same of example, I'm going to assume 10^-5 does matter to you.

It would make sense to write the same test as I outlined above, but where the values are different by order of magnitude 10^-5, and then making sure that there are no different sorts by different providers.

Something along the lines of:

double[] myData = new[]
{
    1.23456,
    1.23457  // difference of 1 * 10 ^-5
}

myProviderA.Save(myData);
myProviderB.Save(myData);

var resultA = myProviderA.GetSorted(myData);
var resultB = myProviderB.GetSorted(myData);

// Assert the lists are in THE SAME order.

Another approach would be to simply round/floor all numbers to the precision that you need them (10^-5 in this example), and then only use those rounded numbers. That way, you don't have to worry about people getting confused that some elements switched their order after inputting it in your system.

This is more of a UX consideration, you'd generally still want to write the above test to make sure that any of your data providers doesn't suddenly start being much more imprecise because of e.g. a software upgrade or a config change from your end.

Answer 5

EDIT: As pointed out by Matthieu M. this solutions doesn't work. I'll leave it here for reference. Bear in mind that if you use this method, 12.034 will be equal to 12.34, and that is a catastrophe. I now think the best method would be using a string in the db and use BigDecimal or similar data types in code, even if this can be less performant.

If you can change the tables' structure and you are sure that all aplications that read and write from it can use the same precision, you could store the integer part of a value in a LONG INT field and the decimals in another.

For example if you have a field called amount, you could change it to amount_int and amount_dec, so 19.433325 becomes amount_int=13; amount_dec=433325.

You could also use a string for this, but you would have to parse it every time.

If you cannot be sure that anyone that writes these fields will use the same precision, or if you don't want to change table structure (and change all applications that use it) then you would have to work with stored real numbers. I can think of two solutions for that:

1. Cut real numbers to the maximum number of decimals that you can trust and always use that (even store that in the DB). This option does not need to modify the db structure but it will modify the stored number removing some precission.

2. Allow a small difference in comparisons. You should read all DBMS manuals to set a difference as small as possible. Something like this (example in Java):

   private final static double MAX_DIFFERENCE=0.000003;
   
   public float valuesEqual(double memoryValue, double storedValue) {
       return Math.abs(memoryValue - storedValue) < MAX_DIFFERENCE;
   }