[Bug]: Formula transform: BigNumber values are passed to POI as strings, breaking comparisons with numeric literals

[sramazzina]

Apache Hop version?

2.16

Java version?

openjdk-17

Operating system

Windows

What happened?

Summary

In the Formula transform, fields of type BigNumber are written into the underlying Apache POI cell as text (HSSFRichTextString) instead of as numeric values. As a result, a formula like [big_field] = 0 is silently evaluated by POI as a string-vs-number comparison and always returns FALSE, even when the BigNumber field actually holds the value 0.

This is surprising and undocumented: the field is declared as a numeric type, displays as a number everywhere else in the pipeline, but cannot be compared against a numeric literal inside Formula.

Affected code

plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParser.java, around lines 110–125:

if (fieldMeta.isString()) {
    cell.setCellValue(rowMeta.getString(dataRow, fieldPosition));
} else if (fieldMeta.isBoolean()) {
    cell.setCellValue(rowMeta.getBoolean(dataRow, fieldPosition));
} else if (fieldMeta.isBigNumber()) {
    cell.setCellValue(new HSSFRichTextString(rowMeta.getString(dataRow, fieldPosition))); // <-- string!
} else if (fieldMeta.isDate()) {
    cell.setCellValue(rowMeta.getDate(dataRow, fieldPosition));
} else if (fieldMeta.isInteger()) {
    cell.setCellValue(rowMeta.getInteger(dataRow, fieldPosition));
} else if (fieldMeta.isNumber()) {
    cell.setCellValue(rowMeta.getNumber(dataRow, fieldPosition));
} else {
    cell.setCellValue(rowMeta.getString(dataRow, fieldPosition));
}

All other numeric types (Integer, Number) are passed to POI as numbers. Only BigNumber is converted to a text value via rowMeta.getString(...).

Root cause

Because the BigNumber branch wraps the value in HSSFRichTextString, the resulting POI cell has type STRING. POI's Excel formula engine does not auto-coerce between strings and numbers for the = operator: in Excel semantics, "0" = 0 evaluates to FALSE. Hence any comparison between a BigNumber field and a numeric literal silently fails.

This has nothing to do with BigDecimal.equals() or scale semantics in Java — Hop never performs a Java-level comparison here; the comparison is entirely delegated to POI and happens on a string operand.

Expected behavior

A BigNumber field holding the value 0 should compare equal to the numeric literal 0 inside a Formula expression, consistently with what a user expects when working with numeric data and with how the other numeric types (Integer, Number) already behave.

Issue Priority

Priority: 3

Issue Component

Component: Transforms