DbUnit: Setting tolerance value - compare SQL Server vs SAP HANA

Question

Most important

DB Unit returns a difference for a double value in row 78:

Exception in thread "main" junit.framework.ComparisonFailure: value (table=dataset, row=78, col=DirtyValue) expected:<4901232.27291950[7]> but was:<4901232.27291950[6]>

So I assume that SQL Server returns 4901232.272919507 while HANA returns 4901232.272919506
(Based on the answer to JUnit assertEquals Changes String)

Then I tried to set the tolerated delta acording to the FAQ Is there an equivalent to JUnit's assertEquals(double expected, double actual, double delta) to define a tolerance level when comparing numeric values?

But I do still get the same error - any ideas?

Additional information

Maybe this is the reason:?

[main] WARN org.dbunit.dataset.AbstractTableMetaData - Potential problem found: The configured data type factory 'class org.dbunit.dataset.datatype.DefaultDataTypeFactory' might cause problems with the current database 'Microsoft SQL Server' (e.g. some datatypes may not be supported properly). In rare cases you might see this message because the list of supported database products is incomplete (list=[derby]). If so please request a java-class update via the forums.If you are using your own IDataTypeFactory extending DefaultDataTypeFactory, ensure that you override getValidDbProducts() to specify the supported database products.
[main] WARN org.dbunit.dataset.AbstractTableMetaData - Potential problem found: The configured data type factory 'class org.dbunit.dataset.datatype.DefaultDataTypeFactory' might cause problems with the current database 'HDB' (e.g. some datatypes may not be supported properly). In rare cases you might see this message because the list of supported database products is incomplete (list=[derby]). If so please request a java-class update via the forums.If you are using your own IDataTypeFactory extending DefaultDataTypeFactory, ensure that you override getValidDbProducts() to specify the supported database products.

DbUnit Version 2.5.4
DirtyValue is calculated from 3 double vales in both systems

SQL Server

SELECT TypeOfGroup, Segment, Portfolio, UniqueID, JobId, DirtyValue, PosUnits, FX_RATE, THEO_Value
FROM DATASET_PL
order by JobId, TypeOfGroup, Segment, Portfolio, UniqueID COLLATE Latin1_General_bin

HANA

SELECT "TypeOfGroup", "Segment", "Portfolio", "UniqueID", "JobId", "DirtyValue", Pos_Units as "PosUnits", FX_RATE, THEO_Value as "THEO_Value"
FROM "_SYS_BIC"."meag.app.h4q.metadata.dataset.pnl/06_COMPARE_CUBES_AND_CALC_ATTR"
order by "JobId", "TypeOfGroup", "Segment", "Portfolio", "UniqueID"

Can you provide the test definition and what version of DB Unit you're using? Also, the involved data definition(s) could be helpful to understand where the difference comes from. Ultimately, you probably want to have the same results on both platforms, irrespective of the `double` implementation. — Lars Br., Sep 01 '17 at 06:48

score 0 · Answer 1 · answered Sep 01 '17 at 11:13

Work-around

Use a diffhandler and handle the differences there:

DiffCollectingFailureHandler diffHandler = new DiffCollectingFailureHandler();
Assertion.assertEquals(expectedTable, actualTable);

List<Difference> diffList = diffHandler.getDiffList();
for (Difference diff: diffList) {
    if (diff.getColumnName().equals("DirtyValue")) {
        double actual = (double) diff.getActualValue();
        double expected = (double) diff.getExpectedValue();
        if (Math.abs(Math.abs(actual) - Math.abs(expected)) > 0.00001) {
            logDiff(diff);
        } else {
            logDebugDiff(diff);
        }
    } else {
        logDiff(diff);
    }
}

private void logDiff(Difference diff) {
    logger.error(String.format("Diff found in row:%s, col:%s expected:%s, actual:%s", diff.getRowIndex(), diff.getColumnName(), diff.getExpectedValue(), diff.getActualValue()));
}

private void logDebugDiff(Difference diff) {
    logger.debug(String.format("Diff found in row:%s, col:%s expected:%s, actual:%s", diff.getRowIndex(), diff.getColumnName(), diff.getExpectedValue(), diff.getActualValue()));
}

Christoph G · Answer 2 · 2017-09-04T09:02:09.513

0

The question was "Any idea?", so maybe it helps to understand why the difference occurrs.

HANA truncates if needed, see "HANA SQL and System Views Reference", numeric types. In HANA the following Statement results in 123.45:

select cast( '123.456' as decimal(6,2)) from dummy;

SQL-Server rounds if needed, at least if the target data type is numeric, see e.g. here at "Truncating and rounding results". The same SQL statement as above results in 123.46 in SQL-Server.

And SQL-Standard seems to leave it open, whether to round or to truncate, see answer on SO . I am not aware of any settings that change the rounding behavior in HANA, but maybe there is.

edited Sep 04 '17 at 09:02

answered Sep 04 '17 at 07:38

Christoph G

555
2
8

This is helpful information about the reasons - THX so far. The question however is how to make DbUnit ignore differences smaller than .00001 – Thorsten Niehues Sep 04 '17 at 09:31
well, with a absolute epsilon of 0.00001 for comparing the delta you would run into the same issues when using a lower precision, but may that is not relevant to you usecase. – Christoph G Sep 04 '17 at 10:36

DbUnit: Setting tolerance value - compare SQL Server vs SAP HANA

2 Answers2