20

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.

11
  • 23
    That's a good conceptual question for a focussed, real-world problem. Still it got 3 close votes from people who don't seem to have understood what the "assistance in explaining, writing or debugging code" close reason is intended for. I hope at least some of them will be responsible enough to come back and consider to retract their vote now.
    – Doc Brown
    Apr 5, 2021 at 9:03
  • 1
    Your answer is surely somewhere in here: docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html ...but only if you have a lot of time on your hands. Apr 5, 2021 at 16:55
  • 1
    Do you ever have deployed production instances that are pulling data in from more than one different DBMS type, or is one deployed instance always only looking at a single DB?
    – Paddy
    Apr 6, 2021 at 10:56
  • 1
    The measurements measure some things, and you have in mind some condition that they do or don't meet. What are they, and what is it? How are you stuck saying that? "tests" & "orderings" & "depend" & "effective " & "automatically" communicate nothing. What is the condition? You need to say what you want, we can't tell you.
    – philipxy
    Apr 6, 2021 at 12:17
  • 1
    You may already be familiar with these, but I don't think any SE question about floating point comparisons is complete without some reference to Knuth's approximatelyEqual and essentiallyEqual. Very useful starting points!
    – Cort Ammon
    Apr 6, 2021 at 14:31

5 Answers 5

15

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.

6
  • I saw your answer after sending mine. In my answer I specify options 1 & 4, I like better my variation of option 4 where I store two integer numbers instead of a string; that way the number of decimals need not be known beforehand.
    – Calabacin
    Apr 5, 2021 at 15:59
  • 1
    @Calabacin: I think before guessing what real number representation could fit best to the OPs case, we should ask the OP for more details about the scoring calculation.
    – Doc Brown
    Apr 5, 2021 at 18:43
  • 3
    For financial data, you likely should not be using floating-point types, but fixed-point ones. Most database systems provide those. Apr 5, 2021 at 23:39
  • 1
    @Calabacin: If by "two numbers" you mean the integer and fractional parts, be very careful about your ordering logic. For example, order these three numbers: [ 1.8 , 1.79 , 1.712 ]. The correct answer is clearly CBA. But now order these three numbers: [ 8 , 79 , 712 ]. Now, the answer is ABC. The length of the fractional matters, you cannot compare them (specifically comparing operations, such as ordering) without taking that into account. So this will be much more effort than just doing select * from mytable order by integerPart, fractionalPart, which is what I suspect you're expecting
    – Flater
    Apr 6, 2021 at 9:52
  • @Paŭlo That's the general wisdom, but in reality it really depends what you're doing with the data. A double has at least 15 significant digits and is much faster than the alternatives (say decimal in C#). There are many use cases where this is perfectly acceptable as long as you round before displaying it to the user. There's lots of code out there that uses doubles that works perfectly fine. Although you might still store it differently in the database for other reasons.
    – Voo
    Apr 6, 2021 at 14:00
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.

0

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.

0

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.

-1

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;
    }
    
3
  • 5
    Rather than storing the decimals separately, he could just store his number as a long integer number of millionths, rather than an integer number of units. Sort of like storing a money value as an integer number of cents rather than a decimal amount of dollars, you get the guaranteed precision of integer math.
    – workerjoe
    Apr 5, 2021 at 19:32
  • 3
    Your solution doesn't work for 12.034 => it's going to store (12, 34) and reassemble them to 12.34. Still, you're on the right track: what you are looking for is Fixed Point representation or a split along the Exponent / Mantissa line. Fixed Point means always storing with a known number of decimals; so for example if you agree that all have 5 decimal digits, then you multiply everything by 10^5 before storing (and divide when retrieving). Note that whether for Fixed Point or Exponent / Mantissa, business requirements should dictate whether to use powers of 2 or 10. Apr 6, 2021 at 10:12
  • Matthieu M. you are right that my system doesn't work in those cases! Although I agree that fixed point is a very good solution, not all DBMS support it so it may not be an option for op. My new favourite option is what @workerjoe purposes: using an integer multiplied by 10^x where X is the desired decimal positions, but there is a risk this causes overflow for big numbers.
    – Calabacin
    Apr 9, 2021 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.