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"

Answer 1

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()));
}

Answer 2

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.