This is an automated email from the ASF dual-hosted git repository.
hansva pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new 68aa61c667 formula optimization (#8304)
68aa61c667 is described below
commit 68aa61c66753ac4b8e055162ef68fdf5a69e9976
Author: Romain Manni-Bucau <[email protected]>
AuthorDate: Tue Oct 13 11:37:03 2026 +0200
formula optimization (#8304)
* [transform][formula] plain java optimization
* Enable to skip optimization - mainly to enable faster adoption
* ensure formula toggle is manageable at runtime too for embedded and
specific cases (+ tests as a side effect)
* fix tests with new api
* review feedback
* review feedbacks
* [formula] add doc about fast path
* switch to config option, and doc cleanup
---------
Co-authored-by: Hans Van Akelyen <[email protected]>
---
config-doc/pom.xml | 13 +
.../ROOT/pages/pipeline/transforms/formula.adoc | 16 +
.../ROOT/partials/config/FormulaConfigPlugin.adoc | 28 +
.../modules/ROOT/partials/config/all-plugins.adoc | 12 +
.../hop/pipeline/transforms/formula/Formula.java | 319 ++++++---
.../transforms/formula/config/FormulaConfig.java | 57 ++
.../formula/config/FormulaConfigPlugin.java | 130 ++++
.../formula/fast/FastFormulaCompiler.java | 211 ++++++
.../formula/fast/FastFormulaEvaluator.java | 759 +++++++++++++++++++++
.../config/messages/messages_en_US.properties | 23 +
.../formula/FormulaFastPathParityTest.java | 384 +++++++++++
.../formula/config/FormulaConfigPluginTest.java | 91 +++
.../formula/fast/FastFormulaCompilerTest.java | 405 +++++++++++
13 files changed, 2372 insertions(+), 76 deletions(-)
diff --git a/config-doc/pom.xml b/config-doc/pom.xml
index 1094648d93..68c539a6a7 100644
--- a/config-doc/pom.xml
+++ b/config-doc/pom.xml
@@ -167,6 +167,19 @@
</exclusion>
</exclusions>
</dependency>
+ <dependency>
+ <groupId>org.apache.hop</groupId>
+ <artifactId>hop-transform-formula</artifactId>
+ <version>${project.version}</version>
+ <scope>provided</scope>
+ <exclusions>
+ <!-- The edge is all that is wanted here, not the classpath
behind it. -->
+ <exclusion>
+ <groupId>*</groupId>
+ <artifactId>*</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
<dependency>
<groupId>org.apache.hop</groupId>
<artifactId>hop-ui</artifactId>
diff --git
a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/formula.adoc
b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/formula.adoc
index ce269e2c61..8c827612f6 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/formula.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/pipeline/transforms/formula.adoc
@@ -102,6 +102,22 @@ See xref:pipeline/formatting-values.adoc[Formatting
numbers and dates].
| Set Null to #N/A | By default, Null values will create a blank cell during
the calculation. Use this option to insert `#N/A` in the cell
|===
+== Fast evaluation
+
+The Formula transform evaluates simple formulas with its own evaluator instead
of Apache POI, which is many times faster. A formula is evaluated this way when
everything in it is on this list:
+
+* the functions `IF`, `AND`, `OR`, `NOT`, `ABS`, `ISBLANK`, `ISNA`, `LEN` and
`TRIM`
+* the operators `+`, `-`, `*` and `/`, `&` to join text, and the comparisons
`=`, `<>`, `<`, `<=`, `>` and `>=`
+* fields like `[field]`, of type String, Number, Integer, BigNumber or Boolean
+* numbers, text in double quotes and `TRUE` or `FALSE`
+
+Every other formula is evaluated with Apache POI, as before. That includes a
formula using a function like `TEXT()`, `DATE()`, `NOW()`, `SUM()`, `CONCAT()`
or `CONCATENATE()`, and a formula using a field of another type, a Date for
example.
+
+The fast evaluation is meant to give the same result as Apache POI. If a
formula returns something different from what it returned in an earlier version
of Hop, switch the fast evaluation off and report the formula:
+
+* For every pipeline, clear *Fast formula evaluation* under *Plugins* ->
*Formula* in the xref:hop-gui/perspective-configuration.adoc[Configuration
perspective], or run `hop-conf.sh --no-formula-fast-path`. This is stored as
`FormulaFastPathEnabled` in `hop-config.json`.
+* For one pipeline, set the variable `HOP_FORMULA_FAST_PATH_ENABLED` to `N`.
+
== Formula Editor
The Formula Editor is opened when you click on the Formula field in the
options table (or go there through tab navigation).
diff --git
a/docs/hop-user-manual/modules/ROOT/partials/config/FormulaConfigPlugin.adoc
b/docs/hop-user-manual/modules/ROOT/partials/config/FormulaConfigPlugin.adoc
new file mode 100644
index 0000000000..907781332f
--- /dev/null
+++ b/docs/hop-user-manual/modules/ROOT/partials/config/FormulaConfigPlugin.adoc
@@ -0,0 +1,28 @@
+////
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+ http://www.apache.org/licenses/LICENSE-2.0
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+////
+// Generated from the @ConfigPlugin and @GuiWidgetElement annotations.
+// Edit those, not this file.
+
+[%header, cols="2,5,1"]
+|===
+|Option|Description|Default
+
+|pass:c[Fast formula evaluation]
+|pass:c[Evaluate the formulas that only use the supported functions and
operators without Apache POI, which is many times faster. Formulas using
anything else are always evaluated with Apache POI. Clear this to evaluate
every formula with Apache POI. A pipeline can also switch it off with the
variable HOP_FORMULA_FAST_PATH_ENABLED set to N.]
+|`pass:c[true]`
+
+|===
diff --git a/docs/hop-user-manual/modules/ROOT/partials/config/all-plugins.adoc
b/docs/hop-user-manual/modules/ROOT/partials/config/all-plugins.adoc
index 5ed37a215d..b0ae75c585 100644
--- a/docs/hop-user-manual/modules/ROOT/partials/config/all-plugins.adoc
+++ b/docs/hop-user-manual/modules/ROOT/partials/config/all-plugins.adoc
@@ -211,6 +211,18 @@ These options are stored under
`pass:c[explorer-perspective]` in `hop-config.jso
|===
+== Formula
+
+[%header, cols="2,5,1"]
+|===
+|Option|Description|Default
+
+|pass:c[Fast formula evaluation]
+|pass:c[Evaluate the formulas that only use the supported functions and
operators without Apache POI, which is many times faster. Formulas using
anything else are always evaluated with Apache POI. Clear this to evaluate
every formula with Apache POI. A pipeline can also switch it off with the
variable HOP_FORMULA_FAST_PATH_ENABLED set to N.]
+|`pass:c[true]`
+
+|===
+
== Git
These options are stored under `pass:c[gitConfig]` in `hop-config.json`.
diff --git
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/Formula.java
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/Formula.java
index 60a0e90bbd..0123225364 100644
---
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/Formula.java
+++
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/Formula.java
@@ -24,6 +24,7 @@ import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.stream.IntStream;
+import org.apache.hop.core.Const;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.exception.HopRuntimeException;
import org.apache.hop.core.exception.HopTransformException;
@@ -37,6 +38,9 @@ import org.apache.hop.pipeline.Pipeline;
import org.apache.hop.pipeline.PipelineMeta;
import org.apache.hop.pipeline.transform.BaseTransform;
import org.apache.hop.pipeline.transform.TransformMeta;
+import org.apache.hop.pipeline.transforms.formula.config.FormulaConfig;
+import org.apache.hop.pipeline.transforms.formula.fast.FastFormulaCompiler;
+import
org.apache.hop.pipeline.transforms.formula.fast.FastFormulaCompiler.CompiledFormula;
import org.apache.hop.pipeline.transforms.formula.util.FormulaParser;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
@@ -48,6 +52,9 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
private FormulaPoi[] poi;
private List<String>[] formulaFieldLists;
+ private CompiledFormula[] fastCompiled;
+ private int[][] fastFieldIndices;
+ private Object[][] fastArgs;
private final HashMap<String, String> replaceMap = new HashMap<>();
@Override
@@ -72,8 +79,10 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
@Override
public void batchComplete() throws HopException {
super.batchComplete();
- for (final var it : poi) {
- it.reset();
+ if (poi != null) {
+ for (final var it : poi) {
+ it.reset();
+ }
}
}
@@ -123,17 +132,56 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
}
}
- // create one backing row per formula
- poi =
- IntStream.range(0, meta.getFormulas().size())
- .mapToObj(it -> new FormulaPoi(this::logDebug))
- .toArray(FormulaPoi[]::new);
- // compute only once for all rows the default field list
- formulaFieldLists =
- meta.getFormulas().stream()
- .map(FormulaMetaFunction::getFormula)
- .map(f -> getFormulaFieldList(resolve(f)))
- .toArray(List[]::new);
+ // compile each formula for the fast path when it is within the
supported subset: the
+ // resolved formula goes through the same variable resolution and field
replacement as the
+ // regular POI path, so both evaluate exactly the same expression. The
compiler returns
+ // NOT_ELIGIBLE (fastPath=false) for anything unsupported, and for
everything when the fast
+ // path is switched off in hop-config.json or with the
HOP_FORMULA_FAST_PATH_ENABLED Hop
+ // variable; in those cases the POI path is used instead.
+ //
+ boolean fastEnabled =
+ FormulaConfig.isFastPathEnabled()
+ &&
Const.toBoolean(getVariable(FastFormulaCompiler.FAST_PATH_VARIABLE, "Y"));
+ int formulaCount = meta.getFormulas().size();
+ fastCompiled = new CompiledFormula[formulaCount];
+ fastFieldIndices = new int[formulaCount][];
+ fastArgs = new Object[formulaCount][];
+ boolean anyPoi = false;
+ for (int i = 0; i < formulaCount; i++) {
+ FormulaMetaFunction fn = meta.getFormulas().get(i);
+ String resolved = resolve(fn.getFormula());
+ String effective = applyReplaceMap(resolved, replaceMap);
+ List<String> effectiveFields = getFormulaFieldList(effective);
+ CompiledFormula compiled =
+ FastFormulaCompiler.compile(
+ effective, effectiveFields, data.outputRowMeta, fn.isSetNa(),
fastEnabled);
+ fastCompiled[i] = compiled;
+ if (compiled.fastPath()) {
+ int[] indices = new int[effectiveFields.size()];
+ for (int j = 0; j < effectiveFields.size(); j++) {
+ int fieldNumber =
data.outputRowMeta.indexOfValue(effectiveFields.get(j));
+ indices[j] = fieldNumber;
+ }
+ fastFieldIndices[i] = indices;
+ fastArgs[i] = new Object[effectiveFields.size()];
+ } else {
+ anyPoi = true;
+ }
+ }
+
+ // POI workbooks are only needed for the formulas that did not take the
fast path.
+ if (anyPoi) {
+ poi =
+ IntStream.range(0, meta.getFormulas().size())
+ .mapToObj(it -> new FormulaPoi(this::logDebug))
+ .toArray(FormulaPoi[]::new);
+ // compute only once for all rows the default field list
+ formulaFieldLists =
+ meta.getFormulas().stream()
+ .map(FormulaMetaFunction::getFormula)
+ .map(f -> getFormulaFieldList(resolve(f)))
+ .toArray(List[]::new);
+ }
}
int tempIndex = getInputRowMeta().size();
@@ -146,70 +194,82 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
for (int i = 0; i < meta.getFormulas().size(); i++) {
Object outputValue = null;
FormulaMetaFunction formula = meta.getFormulas().get(i);
- FormulaParser parser =
- new FormulaParser(
- formula,
- data.outputRowMeta,
- outputRowData,
- poi[i],
- variables,
- replaceMap,
- formulaFieldLists[i]);
+ int outputValueType = formula.getValueType();
+ CompiledFormula compiled = fastCompiled[i];
try {
- CellValue cellValue = parser.getFormulaValue();
- CellType cellType = cellValue.getCellType();
-
- int outputValueType = formula.getValueType();
- switch (cellType) {
- case BLANK:
- // should never happen.
- break;
- case NUMERIC:
- outputValue = cellValue.getNumberValue();
- switch (outputValueType) {
- case IValueMeta.TYPE_NUMBER:
- data.returnType[i] = FormulaData.RETURN_TYPE_NUMBER;
- formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
- break;
- case IValueMeta.TYPE_INTEGER:
- data.returnType[i] = FormulaData.RETURN_TYPE_INTEGER;
- formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
- break;
- case IValueMeta.TYPE_BIGNUMBER:
- data.returnType[i] = FormulaData.RETURN_TYPE_BIGDECIMAL;
- formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
- break;
- case IValueMeta.TYPE_DATE:
- outputValue = DateUtil.getJavaDate(cellValue.getNumberValue());
- data.returnType[i] = FormulaData.RETURN_TYPE_DATE;
- formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
- break;
- case IValueMeta.TYPE_TIMESTAMP:
- outputValue =
-
Timestamp.from(DateUtil.getJavaDate(cellValue.getNumberValue()).toInstant());
- data.returnType[i] = FormulaData.RETURN_TYPE_TIMESTAMP;
- formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
- break;
- default:
- break;
- }
- // get cell value
- break;
- case BOOLEAN:
- outputValue = cellValue.getBooleanValue();
- data.returnType[i] = FormulaData.RETURN_TYPE_BOOLEAN;
- formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_BOOLEAN);
- break;
- case STRING:
- outputValue = cellValue.getStringValue();
- data.returnType[i] = FormulaData.RETURN_TYPE_STRING;
- formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_STRING);
- break;
- case ERROR:
- outputValue = getErrorValue(cellValue, formula);
- break;
- default:
- break;
+ if (compiled != null && compiled.fastPath()) {
+ // Fast path: no POI workbook or worksheet, just run the compiled
plain-Java tree. The
+ // argument array and the field indices are reused for every row to
avoid per-row lookups
+ // and allocations.
+ Object[] args =
+ buildFastArguments(
+ fastArgs[i], fastFieldIndices[i], outputRowData,
formula.isSetNa());
+ Object formulaResult = compiled.function().apply(args);
+ outputValue = mapFastResult(formulaResult, outputValueType, i,
formula);
+ } else {
+ FormulaParser parser =
+ new FormulaParser(
+ formula,
+ data.outputRowMeta,
+ outputRowData,
+ poi[i],
+ variables,
+ replaceMap,
+ formulaFieldLists[i]);
+ CellValue cellValue = parser.getFormulaValue();
+ CellType cellType = cellValue.getCellType();
+
+ switch (cellType) {
+ case BLANK:
+ // should never happen.
+ break;
+ case NUMERIC:
+ outputValue = cellValue.getNumberValue();
+ switch (outputValueType) {
+ case IValueMeta.TYPE_NUMBER:
+ data.returnType[i] = FormulaData.RETURN_TYPE_NUMBER;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ case IValueMeta.TYPE_INTEGER:
+ data.returnType[i] = FormulaData.RETURN_TYPE_INTEGER;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ case IValueMeta.TYPE_BIGNUMBER:
+ data.returnType[i] = FormulaData.RETURN_TYPE_BIGDECIMAL;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ case IValueMeta.TYPE_DATE:
+ outputValue =
DateUtil.getJavaDate(cellValue.getNumberValue());
+ data.returnType[i] = FormulaData.RETURN_TYPE_DATE;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ case IValueMeta.TYPE_TIMESTAMP:
+ outputValue =
+
Timestamp.from(DateUtil.getJavaDate(cellValue.getNumberValue()).toInstant());
+ data.returnType[i] = FormulaData.RETURN_TYPE_TIMESTAMP;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_NUMBER);
+ break;
+ default:
+ break;
+ }
+ // get cell value
+ break;
+ case BOOLEAN:
+ outputValue = cellValue.getBooleanValue();
+ data.returnType[i] = FormulaData.RETURN_TYPE_BOOLEAN;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_BOOLEAN);
+ break;
+ case STRING:
+ outputValue = cellValue.getStringValue();
+ data.returnType[i] = FormulaData.RETURN_TYPE_STRING;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_STRING);
+ break;
+ case ERROR:
+ outputValue = getErrorValue(cellValue, formula);
+ break;
+ default:
+ break;
+ }
}
int realIndex = (data.replaceIndex[i] < 0) ? tempIndex++ :
data.replaceIndex[i];
@@ -236,6 +296,14 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
return true;
}
+ /**
+ * The compiled formula for the given formula index, so tests can assert the
fast path was
+ * actually taken instead of silently comparing two POI evaluations.
+ */
+ CompiledFormula compiledFormula(int index) {
+ return fastCompiled[index];
+ }
+
/**
* This is the base transform that forms that basis for all transforms. You
can derive from this
* class to implement your own transforms.
@@ -363,4 +431,103 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
IValueMeta actual =
ValueMetaFactory.guessValueMetaInterface(formulaResult);
return target.convertData(actual, formulaResult);
}
+
+ /**
+ * Applies the "replace field" mapping to a formula, mirroring what {@link
FormulaParser} does so
+ * the fast path evaluates the exact same expression. Formula fields are
substituted by their
+ * replacement column, e.g. {@code [aliasAmount]} becomes {@code
[realAmount]}.
+ *
+ * @param formula the variable-resolved formula
+ * @param replacements the field to replacement mapping
+ * @return the formula with replacements applied
+ */
+ private static String applyReplaceMap(String formula, HashMap<String,
String> replacements) {
+ String effective = formula;
+ for (String field : getFormulaFieldList(formula)) {
+ String replacement = replacements.get(field);
+ if (replacement != null) {
+ effective = effective.replace("[" + field + "]", "[" + replacement +
"]");
+ }
+ }
+ return effective;
+ }
+
+ /**
+ * Fills the reusable argument array for a fast-path formula from the row
data. A null field bound
+ * with the "#N/A" option is passed as the {@link FastFormulaCompiler#NA}
marker so the functions
+ * can tell a blank cell from an error cell, exactly like the POI path. The
field indices were
+ * pre-computed in {@code first}, so no per-row metadata lookup is needed.
+ *
+ * @param args the reusable argument array to fill
+ * @param fieldIndices the pre-computed position of each referenced field in
the output row
+ * @param sourceRow the output row data
+ * @param setNa whether null fields are turned into {@code #N/A}
+ * @return the {@code args} array, filled for this row
+ */
+ private Object[] buildFastArguments(
+ Object[] args, int[] fieldIndices, Object[] sourceRow, boolean setNa) {
+ for (int i = 0; i < fieldIndices.length; i++) {
+ int fieldIndex = fieldIndices[i];
+ Object value = fieldIndex < 0 ? null : sourceRow[fieldIndex];
+ args[i] = (value == null && setNa) ? FastFormulaCompiler.NA : value;
+ }
+ return args;
+ }
+
+ /**
+ * Maps a plain-Java fast-path result to the output value and {@code
returnType} the way the POI
+ * {@code CellType} switch would, so both paths produce identical output.
Numbers become numeric
+ * cells, booleans become boolean cells and strings become string cells; a
null result (a blank or
+ * {@code #N/A} cell) stays null.
+ *
+ * <p>Replicating the switch rather than refactoring the POI branch keeps
the existing behavior
+ * untouched; a null {@code returnType} of {@code -1} makes {@link
#getReturnValue} return null.
+ */
+ private Object mapFastResult(
+ Object formulaResult, int outputValueType, int i, FormulaMetaFunction
formula) {
+ if (formulaResult == null) {
+ data.returnType[i] = -1;
+ return null;
+ }
+ if (formulaResult instanceof Number number) {
+ double cellNumberValue = number.doubleValue();
+ switch (outputValueType) {
+ case IValueMeta.TYPE_NUMBER:
+ data.returnType[i] = FormulaData.RETURN_TYPE_NUMBER;
+ formula.setNeedDataConversion(false);
+ return cellNumberValue;
+ case IValueMeta.TYPE_INTEGER:
+ data.returnType[i] = FormulaData.RETURN_TYPE_INTEGER;
+ formula.setNeedDataConversion(true);
+ return cellNumberValue;
+ case IValueMeta.TYPE_BIGNUMBER:
+ data.returnType[i] = FormulaData.RETURN_TYPE_BIGDECIMAL;
+ formula.setNeedDataConversion(true);
+ return cellNumberValue;
+ case IValueMeta.TYPE_DATE:
+ data.returnType[i] = FormulaData.RETURN_TYPE_DATE;
+ formula.setNeedDataConversion(true);
+ return DateUtil.getJavaDate(cellNumberValue);
+ case IValueMeta.TYPE_TIMESTAMP:
+ data.returnType[i] = FormulaData.RETURN_TYPE_TIMESTAMP;
+ formula.setNeedDataConversion(true);
+ return
Timestamp.from(DateUtil.getJavaDate(cellNumberValue).toInstant());
+ default:
+ data.returnType[i] = -1;
+ return cellNumberValue;
+ }
+ }
+ if (formulaResult instanceof Boolean booleanValue) {
+ data.returnType[i] = FormulaData.RETURN_TYPE_BOOLEAN;
+ formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_BOOLEAN);
+ return booleanValue;
+ }
+ if (formulaResult instanceof String stringValue) {
+ data.returnType[i] = FormulaData.RETURN_TYPE_STRING;
+ formula.setNeedDataConversion(outputValueType != IValueMeta.TYPE_STRING);
+ return stringValue;
+ }
+ data.returnType[i] = -1;
+ return null;
+ }
}
diff --git
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/config/FormulaConfig.java
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/config/FormulaConfig.java
new file mode 100644
index 0000000000..2b18d30a63
--- /dev/null
+++
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/config/FormulaConfig.java
@@ -0,0 +1,57 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.formula.config;
+
+import org.apache.hop.core.config.HopConfig;
+
+/**
+ * The options of the Formula transform in {@code hop-config.json}, edited in
the configuration
+ * perspective and with {@code hop conf} through {@link FormulaConfigPlugin}.
+ *
+ * <p>Kept apart from that plugin on purpose: the plugin is a Hop GUI
listener, and the transform
+ * reads these options wherever it runs, Hop GUI or not.
+ */
+public final class FormulaConfig {
+
+ /** Whether the Formula transform may use the fast evaluator at all; true
when not set. */
+ public static final String OPTION_FAST_PATH_ENABLED =
"FormulaFastPathEnabled";
+
+ private FormulaConfig() {}
+
+ /**
+ * @return false when the fast evaluator was switched off in {@code
hop-config.json}
+ */
+ public static boolean isFastPathEnabled() {
+ try {
+ return HopConfig.readOptionBoolean(OPTION_FAST_PATH_ENABLED, true);
+ } catch (Exception e) {
+ // No readable hop-config.json, on a remote worker say: the default
stands.
+ return true;
+ }
+ }
+
+ /**
+ * Switch the fast evaluator on or off for every pipeline, and save that in
{@code
+ * hop-config.json}.
+ *
+ * @param enabled false to always evaluate with Apache POI
+ */
+ public static void setFastPathEnabled(boolean enabled) {
+ HopConfig.getInstance().saveOption(OPTION_FAST_PATH_ENABLED, enabled);
+ }
+}
diff --git
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/config/FormulaConfigPlugin.java
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/config/FormulaConfigPlugin.java
new file mode 100644
index 0000000000..d8f3c297f1
--- /dev/null
+++
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/config/FormulaConfigPlugin.java
@@ -0,0 +1,130 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.formula.config;
+
+import lombok.Getter;
+import lombok.Setter;
+import org.apache.hop.core.config.plugin.ConfigPlugin;
+import org.apache.hop.core.config.plugin.IConfigOptions;
+import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.gui.plugin.GuiElementType;
+import org.apache.hop.core.gui.plugin.GuiPlugin;
+import org.apache.hop.core.gui.plugin.GuiWidgetElement;
+import org.apache.hop.core.logging.ILogChannel;
+import org.apache.hop.core.variables.IVariables;
+import org.apache.hop.i18n.BaseMessages;
+import org.apache.hop.metadata.api.IHasHopMetadataProvider;
+import org.apache.hop.ui.core.dialog.ErrorDialog;
+import org.apache.hop.ui.core.gui.GuiCompositeWidgets;
+import org.apache.hop.ui.core.gui.IGuiPluginCompositeWidgetsListener;
+import org.apache.hop.ui.hopgui.HopGui;
+import
org.apache.hop.ui.hopgui.perspective.configuration.tabs.ConfigPluginOptionsTab;
+import org.eclipse.swt.widgets.Button;
+import org.eclipse.swt.widgets.Control;
+import picocli.CommandLine;
+
+/**
+ * The Formula transform options in the configuration perspective and in
{@code hop conf}. The
+ * values themselves live in {@link FormulaConfig}.
+ */
+@Getter
+@Setter
+@ConfigPlugin(id = "FormulaConfigPlugin", description = "Formula transform
options")
+@GuiPlugin(description = "i18n::FormulaConfigPlugin.Name")
+public class FormulaConfigPlugin implements IConfigOptions,
IGuiPluginCompositeWidgetsListener {
+
+ private static final Class<?> PKG = FormulaConfigPlugin.class;
+
+ private static final String WIDGET_ID_FAST_PATH_ENABLED =
"10000-formula-fast-path-enabled";
+
+ @GuiWidgetElement(
+ id = WIDGET_ID_FAST_PATH_ENABLED,
+ parentId = ConfigPluginOptionsTab.GUI_WIDGETS_PARENT_ID,
+ type = GuiElementType.CHECKBOX,
+ variables = false,
+ defaultValue = "true",
+ label = "i18n::FormulaConfigPlugin.FastPathEnabled.Label",
+ toolTip = "i18n::FormulaConfigPlugin.FastPathEnabled.Tooltip")
+ @CommandLine.Option(
+ names = {"--formula-fast-path"},
+ negatable = true,
+ description =
+ "Let the Formula transform evaluate the formulas it can without
Apache POI."
+ + " --no-formula-fast-path always uses Apache POI")
+ private Boolean fastPathEnabled;
+
+ public static FormulaConfigPlugin getInstance() {
+ FormulaConfigPlugin instance = new FormulaConfigPlugin();
+ instance.fastPathEnabled = FormulaConfig.isFastPathEnabled();
+ return instance;
+ }
+
+ @Override
+ public boolean handleOption(
+ ILogChannel log, IHasHopMetadataProvider hasHopMetadataProvider,
IVariables variables)
+ throws HopException {
+ if (fastPathEnabled == null) {
+ return false;
+ }
+ try {
+ FormulaConfig.setFastPathEnabled(fastPathEnabled);
+ } catch (Exception e) {
+ throw new HopException("Error saving the Formula transform options", e);
+ }
+ if (fastPathEnabled) {
+ log.logBasic("The Formula transform evaluates the formulas it can
without Apache POI");
+ } else {
+ log.logBasic("The Formula transform evaluates every formula with Apache
POI");
+ }
+ return true;
+ }
+
+ @Override
+ public void widgetsCreated(GuiCompositeWidgets compositeWidgets) {
+ // Nothing to do
+ }
+
+ @Override
+ public void widgetsPopulated(GuiCompositeWidgets compositeWidgets) {
+ // Nothing to do
+ }
+
+ @Override
+ public void widgetModified(
+ GuiCompositeWidgets compositeWidgets, Control changedWidget, String
widgetId) {
+ persistContents(compositeWidgets);
+ }
+
+ @Override
+ public void persistContents(GuiCompositeWidgets compositeWidgets) {
+ if (!(compositeWidgets.getWidgetsMap().get(WIDGET_ID_FAST_PATH_ENABLED)
+ instanceof Button button)) {
+ return;
+ }
+ fastPathEnabled = button.getSelection();
+ try {
+ FormulaConfig.setFastPathEnabled(fastPathEnabled);
+ } catch (Exception e) {
+ new ErrorDialog(
+ HopGui.getInstance().getShell(),
+ BaseMessages.getString(PKG,
"FormulaConfigPlugin.SavingOption.ErrorDialog.Header"),
+ BaseMessages.getString(PKG,
"FormulaConfigPlugin.SavingOption.ErrorDialog.Message"),
+ e);
+ }
+ }
+}
diff --git
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/fast/FastFormulaCompiler.java
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/fast/FastFormulaCompiler.java
new file mode 100644
index 0000000000..a107cff71a
--- /dev/null
+++
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/fast/FastFormulaCompiler.java
@@ -0,0 +1,211 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.hop.pipeline.transforms.formula.fast;
+
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.function.Function;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.IValueMeta;
+import
org.apache.hop.pipeline.transforms.formula.fast.FastFormulaEvaluator.Node;
+
+/**
+ * Decides whether a formula can run on the plain-Java fast path and, when it
can, compiles it once
+ * into a reusable {@link Function} and caches the result.
+ *
+ * <p>The cache is a fixed-size LRU keyed on the resolved formula together
with the value types of
+ * the fields it references. The {@code setNa} flag is deliberately not part
of the key because it
+ * only affects per-row argument building, not the compiled AST. Both the
compile cost and the
+ * eligibility decision are cached, so a pipeline only pays the parse once per
distinct formula.
+ *
+ * <p>The maximum number of cached entries is a {@code -D} property, {@value
#MAX_SIZE_PROPERTY},
+ * and defaults to {@value #DEFAULT_MAX_SIZE}. Whether the fast path is used
at all is up to the
+ * caller: the Formula transform switches it off when the {@code
hop-config.json} option of {@link
+ * org.apache.hop.pipeline.transforms.formula.config.FormulaConfig} or the
{@link
+ * #FAST_PATH_VARIABLE} Hop variable says so.
+ */
+public final class FastFormulaCompiler {
+
+ public static final String MAX_SIZE_PROPERTY =
+
"org.apache.hop.pipeline.transforms.formula.fast.FastFormulaCompiler.maxSize";
+
+ /**
+ * Hop variable that switches the fast path off for one pipeline when set to
{@code N}. The
+ * Formula transform reads it and passes the outcome to {@link #compile}.
+ */
+ public static final String FAST_PATH_VARIABLE =
"HOP_FORMULA_FAST_PATH_ENABLED";
+
+ public static final int DEFAULT_MAX_SIZE = 1024;
+
+ /** Binds a formula to its compiled function (or states it is not eligible).
*/
+ public record CompiledFormula(boolean fastPath, Function<Object[], Object>
function) {
+ public static final CompiledFormula NOT_ELIGIBLE = new
CompiledFormula(false, null);
+ }
+
+ /** Sentinel put in the args array for a null field bound with the "#N/A"
option. */
+ public static final Object NA = new Object();
+
+ private static final Map<String, CompiledFormula> CACHE =
createLru(maxSize());
+
+ private FastFormulaCompiler() {}
+
+ /**
+ * Returns the cached result for a formula, computing and caching it when
not present.
+ *
+ * @param resolvedFormula the variable-resolved formula, ready to be parsed
+ * @param fieldNames the fields the formula references, in argument order
+ * @param rowMeta the row metadata, used for the value types of the fields
+ * @param setNa whether null fields are turned into {@code #N/A}
+ * @return the compiled formula, whose {@link CompiledFormula#fastPath()} is
false when the
+ * formula is out of the fast-path subset or the fast path is disabled
+ */
+ public static CompiledFormula compile(
+ String resolvedFormula, List<String> fieldNames, IRowMeta rowMeta,
boolean setNa) {
+ return compile(resolvedFormula, fieldNames, rowMeta, setNa, true);
+ }
+
+ /**
+ * Returns the cached result for a formula, computing and caching it when
not present.
+ *
+ * @param resolvedFormula the variable-resolved formula, ready to be parsed
+ * @param fieldNames the fields the formula references, in argument order
+ * @param rowMeta the row metadata, used for the value types of the fields
+ * @param setNa whether null fields are turned into {@code #N/A}
+ * @param enabled whether the caller wants the fast path at all; the Formula
transform reads the
+ * {@code hop-config.json} option and the {@link #FAST_PATH_VARIABLE}
Hop variable into this
+ * @return the compiled formula, whose {@link CompiledFormula#fastPath()} is
false when the
+ * formula is out of the fast-path subset or the caller disabled the
fast path
+ */
+ public static CompiledFormula compile(
+ String resolvedFormula,
+ List<String> fieldNames,
+ IRowMeta rowMeta,
+ boolean setNa,
+ boolean enabled) {
+ if (!enabled) {
+ return CompiledFormula.NOT_ELIGIBLE;
+ }
+ int[] indices = eligibleIndices(fieldNames, rowMeta);
+ if (indices == null) {
+ return CompiledFormula.NOT_ELIGIBLE;
+ }
+ // The setNa flag does not influence the compiled AST: it is only applied
when building the
+ // arguments per row, so it is deliberately kept out of the cache key.
+ String key = key(resolvedFormula, fieldNames, rowMeta, indices);
+ synchronized (CACHE) {
+ CompiledFormula cached = CACHE.get(key);
+ if (cached != null) {
+ return cached;
+ }
+ }
+ CompiledFormula compiled = compileUncached(resolvedFormula, fieldNames);
+ synchronized (CACHE) {
+ CACHE.put(key, compiled);
+ }
+ return compiled;
+ }
+
+ private static CompiledFormula compileUncached(String resolvedFormula,
List<String> fieldNames) {
+ Map<String, Integer> fieldIndex = new LinkedHashMap<>();
+ for (int i = 0; i < fieldNames.size(); i++) {
+ fieldIndex.putIfAbsent(fieldNames.get(i), i);
+ }
+ final Node root;
+ try {
+ root = FastFormulaEvaluator.parse(resolvedFormula, fieldIndex);
+ } catch (RuntimeException e) {
+ // Any parse-time failure (unsupported construct, malformed literal,
etc.) means the formula
+ // is not eligible; fall back to the POI path instead of crashing
initialization.
+ return CompiledFormula.NOT_ELIGIBLE;
+ }
+ // The function is a thin closure over the already-parsed tree: calling it
only runs the tree.
+ Function<Object[], Object> function = root::eval;
+ return new CompiledFormula(true, function);
+ }
+
+ /**
+ * Resolves every referenced field to its position in the row metadata, or
returns {@code null}
+ * when any field is missing from the row or holds a value type the fast
path can not read
+ * natively. Field typos and bracketed string literals (whose content is
also seen as a field by
+ * the shared extractor) must never crash with an index error, so a missing
field simply makes the
+ * formula not eligible.
+ */
+ private static int[] eligibleIndices(List<String> fieldNames, IRowMeta
rowMeta) {
+ int[] indices = new int[fieldNames.size()];
+ for (int i = 0; i < fieldNames.size(); i++) {
+ int fieldNumber = rowMeta.indexOfValue(fieldNames.get(i));
+ if (fieldNumber < 0 ||
!isFastType(rowMeta.getValueMeta(fieldNumber).getType())) {
+ return null;
+ }
+ indices[i] = fieldNumber;
+ }
+ return indices;
+ }
+
+ private static boolean isFastType(int type) {
+ switch (type) {
+ case IValueMeta.TYPE_STRING,
+ IValueMeta.TYPE_NUMBER,
+ IValueMeta.TYPE_INTEGER,
+ IValueMeta.TYPE_BIGNUMBER,
+ IValueMeta.TYPE_BOOLEAN:
+ return true;
+ default:
+ return false;
+ }
+ }
+
+ private static String key(
+ String resolvedFormula, List<String> fieldNames, IRowMeta rowMeta, int[]
indices) {
+ StringBuilder key = new StringBuilder(resolvedFormula.length() +
fieldNames.size() * 12 + 3);
+ key.append(resolvedFormula);
+ for (int i = 0; i < fieldNames.size(); i++) {
+ key.append('|').append(fieldNames.get(i));
+ key.append(':').append(rowMeta.getValueMeta(indices[i]).getType());
+ }
+ return key.toString();
+ }
+
+ private static int maxSize() {
+ int size = Integer.getInteger(MAX_SIZE_PROPERTY, DEFAULT_MAX_SIZE);
+ return Math.max(1, size);
+ }
+
+ private static Map<String, CompiledFormula> createLru(int maxSize) {
+ return new LinkedHashMap<>(16, 0.75f, true) {
+ @Override
+ protected boolean removeEldestEntry(Map.Entry<String, CompiledFormula>
eldest) {
+ return size() > maxSize;
+ }
+ };
+ }
+
+ /** Clears the cache. Intended for tests. */
+ public static void clear() {
+ synchronized (CACHE) {
+ CACHE.clear();
+ }
+ }
+
+ /** The current size of the cache (when the max size was read at class
load). */
+ static int cacheSize() {
+ synchronized (CACHE) {
+ return CACHE.size();
+ }
+ }
+}
diff --git
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/fast/FastFormulaEvaluator.java
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/fast/FastFormulaEvaluator.java
new file mode 100644
index 0000000000..f2a0402cf0
--- /dev/null
+++
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/fast/FastFormulaEvaluator.java
@@ -0,0 +1,759 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.hop.pipeline.transforms.formula.fast;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * The pure-Java compiler for the Formula transform fast path.
+ *
+ * <p>A formula that only uses the supported subset (field references,
numbers, strings, booleans,
+ * the {@code + - * /} and {@code &} binary operators, comparisons, and the
Excel functions {@code
+ * IF}, {@code AND}, {@code OR}, {@code NOT}, {@code ABS}, {@code ISBLANK},
{@code ISNA}, {@code
+ * LEN} and {@code TRIM}) is parsed once into a tree of {@link Node}s. Every
row then just runs the
+ * tree against the field values, which is orders of magnitude faster than
going through a POI
+ * workbook and worksheet.
+ *
+ * <p>Values behave the way POI evaluates them: a blank cell is 0 in
arithmetic and FALSE in a
+ * boolean context, booleans are numeric (TRUE is 1) and rank above text above
numbers in ordered
+ * comparisons, and TRIM only trims characters up to the ASCII space and
collapses ASCII spaces.
+ *
+ * <p>An {@code #N/A} error operand, produced by the "Set Null to #N/A"
option, propagates through
+ * every operation and function except {@code ISNA}, which is the only way to
test for it.
+ *
+ * <p>Anything outside that subset makes {@link #parse} throw, which {@link
FastFormulaCompiler}
+ * turns into "not eligible for the fast path"; the transform then falls back
to the regular POI
+ * evaluation.
+ */
+final class FastFormulaEvaluator {
+
+ private FastFormulaEvaluator() {}
+
+ /**
+ * Parses a formula into an executable tree.
+ *
+ * @param expression the variable-resolved formula
+ * @param fieldIndex maps a field name to the position it takes in the
{@code args} array handed
+ * to {@link Node#eval(Object[])}
+ * @return the root node
+ * @throws UnsupportedFormulaException when the formula uses a construct
outside the fast path
+ * subset, meaning it is not eligible
+ */
+ static Node parse(String expression, Map<String, Integer> fieldIndex) {
+ return new Parser(expression, fieldIndex).parseExpression();
+ }
+
+ abstract static class Node {
+ abstract Object eval(Object[] args);
+ }
+
+ /** A literal number, string or boolean. */
+ private static final class LiteralNode extends Node {
+ private final Object value;
+
+ private LiteralNode(Object value) {
+ this.value = value;
+ }
+
+ @Override
+ Object eval(Object[] args) {
+ return value;
+ }
+ }
+
+ /** A reference to one of the row fields, read from the position it was
bound to. */
+ static final class FieldNode extends Node {
+ private final int index;
+
+ FieldNode(int index) {
+ this.index = index;
+ }
+
+ @Override
+ Object eval(Object[] args) {
+ return args[index];
+ }
+ }
+
+ /** A unary minus. */
+ private static final class UnaryNode extends Node {
+ private final Node operand;
+
+ private UnaryNode(Node operand) {
+ this.operand = operand;
+ }
+
+ @Override
+ Object eval(Object[] args) {
+ Object value = operand.eval(args);
+ if (value == FastFormulaCompiler.NA) {
+ return FastFormulaCompiler.NA;
+ }
+ return -toNumber(value);
+ }
+ }
+
+ private enum BinaryOp {
+ ADD,
+ SUBTRACT,
+ MULTIPLY,
+ DIVIDE,
+ CONCAT,
+ EQUAL,
+ NOT_EQUAL,
+ GREATER,
+ GREATER_OR_EQUAL,
+ LESS,
+ LESS_OR_EQUAL
+ }
+
+ /** A binary operation between two operand nodes. */
+ private static final class BinaryNode extends Node {
+ private final Node left;
+ private final Node right;
+ private final BinaryOp op;
+
+ private BinaryNode(Node left, Node right, BinaryOp op) {
+ this.left = left;
+ this.right = right;
+ this.op = op;
+ }
+
+ @Override
+ Object eval(Object[] args) {
+ Object left = this.left.eval(args);
+ Object right = this.right.eval(args);
+ if (left == FastFormulaCompiler.NA || right == FastFormulaCompiler.NA) {
+ // Excel and POI propagate an error operand through every operation
instead of evaluating
+ // it, so the NA sentinel short-circuits the whole expression.
+ return FastFormulaCompiler.NA;
+ }
+ switch (op) {
+ case ADD:
+ return toNumber(left) + toNumber(right);
+ case SUBTRACT:
+ return toNumber(left) - toNumber(right);
+ case MULTIPLY:
+ return toNumber(left) * toNumber(right);
+ case DIVIDE:
+ {
+ double divisor = toNumber(right);
+ if (divisor == 0.0d) {
+ throw new ArithmeticException("Division by zero (#DIV/0!)");
+ }
+ return toNumber(left) / divisor;
+ }
+ case CONCAT:
+ return TextValue.of(left) + TextValue.of(right);
+ case EQUAL:
+ return compareEqual(left, right);
+ case NOT_EQUAL:
+ return !compareEqual(left, right);
+ case GREATER:
+ return compareOrdered(left, right) > 0;
+ case GREATER_OR_EQUAL:
+ return compareOrdered(left, right) >= 0;
+ case LESS:
+ return compareOrdered(left, right) < 0;
+ case LESS_OR_EQUAL:
+ return compareOrdered(left, right) <= 0;
+ default:
+ throw new UnsupportedFormulaException("Unsupported operator " + op);
+ }
+ }
+ }
+
+ /** A call to one of the supported Excel functions. */
+ private static final class FunctionNode extends Node {
+ private final String name;
+ private final List<Node> arguments;
+
+ private FunctionNode(String name, List<Node> arguments) {
+ this.name = name;
+ this.arguments = arguments;
+ }
+
+ @Override
+ Object eval(Object[] args) {
+ switch (name) {
+ case "IF":
+ // Excel allows a 2-argument IF: the (omitted) false branch
evaluates to FALSE.
+ if (arguments.size() != 2 && arguments.size() != 3) {
+ throw new UnsupportedFormulaException("IF requires 2 or 3
arguments");
+ }
+ Object condition = arguments.get(0).eval(args);
+ if (condition == FastFormulaCompiler.NA) {
+ return FastFormulaCompiler.NA;
+ }
+ // Only the taken branch is evaluated: an error in the other one
stays invisible.
+ return toBoolean(condition)
+ ? arguments.get(1).eval(args)
+ : (arguments.size() == 3 ? arguments.get(2).eval(args) :
Boolean.FALSE);
+ case "AND":
+ boolean andResult = true;
+ for (Node argument : arguments) {
+ Object value = argument.eval(args);
+ if (value == FastFormulaCompiler.NA) {
+ return FastFormulaCompiler.NA;
+ }
+ andResult &= toBoolean(value);
+ }
+ return andResult;
+ case "OR":
+ boolean orResult = false;
+ for (Node argument : arguments) {
+ Object value = argument.eval(args);
+ if (value == FastFormulaCompiler.NA) {
+ return FastFormulaCompiler.NA;
+ }
+ orResult |= toBoolean(value);
+ }
+ return orResult;
+ case "NOT":
+ if (arguments.size() != 1) {
+ throw new UnsupportedFormulaException("NOT requires 1 argument");
+ }
+ Object operand = arguments.get(0).eval(args);
+ if (operand == FastFormulaCompiler.NA) {
+ return FastFormulaCompiler.NA;
+ }
+ return !toBoolean(operand);
+ case "ABS":
+ if (arguments.size() != 1) {
+ throw new UnsupportedFormulaException("ABS requires 1 argument");
+ }
+ Object absOperand = arguments.get(0).eval(args);
+ if (absOperand == FastFormulaCompiler.NA) {
+ return FastFormulaCompiler.NA;
+ }
+ return Math.abs(toNumber(absOperand));
+ case "ISBLANK":
+ if (arguments.size() != 1) {
+ throw new UnsupportedFormulaException("ISBLANK requires 1
argument");
+ }
+ // An error cell is not blank, so the NA sentinel flows through
unchanged.
+ return isBlank(arguments.get(0).eval(args));
+ case "ISNA":
+ if (arguments.size() != 1) {
+ throw new UnsupportedFormulaException("ISNA requires 1 argument");
+ }
+ return arguments.get(0).eval(args) == FastFormulaCompiler.NA;
+ case "LEN":
+ if (arguments.size() != 1) {
+ throw new UnsupportedFormulaException("LEN requires 1 argument");
+ }
+ Object lenOperand = arguments.get(0).eval(args);
+ if (lenOperand == FastFormulaCompiler.NA) {
+ return FastFormulaCompiler.NA;
+ }
+ return (double) TextValue.of(lenOperand).length();
+ case "TRIM":
+ if (arguments.size() != 1) {
+ throw new UnsupportedFormulaException("TRIM requires 1 argument");
+ }
+ Object trimOperand = arguments.get(0).eval(args);
+ if (trimOperand == FastFormulaCompiler.NA) {
+ return FastFormulaCompiler.NA;
+ }
+ return trim(trimOperand);
+ default:
+ throw new UnsupportedFormulaException("Unsupported function " +
name);
+ }
+ }
+ }
+
+ private static boolean isBlank(Object value) {
+ // A null field bound without the #N/A option is a blank cell; a field
bound as #N/A is an
+ // error cell, which is not blank.
+ return value == null;
+ }
+
+ /**
+ * Excel TRIM as POI implements it ({@code arg.trim().replaceAll(" +", "
")}): characters up to
+ * and including the ASCII space are stripped at both ends and runs of ASCII
spaces collapse to a
+ * single space. Any other whitespace, like a tab, is only stripped at the
ends and kept as-is in
+ * the middle.
+ */
+ private static String trim(Object value) {
+ String text = TextValue.of(value);
+ int start = 0;
+ int end = text.length();
+ while (start < end && text.charAt(start) <= ' ') {
+ start++;
+ }
+ while (end > start && text.charAt(end - 1) <= ' ') {
+ end--;
+ }
+ StringBuilder out = new StringBuilder(end - start);
+ boolean lastWasSpace = true;
+ for (int i = start; i < end; i++) {
+ char c = text.charAt(i);
+ if (c == ' ') {
+ if (!lastWasSpace) {
+ out.append(' ');
+ }
+ lastWasSpace = true;
+ } else {
+ out.append(c);
+ lastWasSpace = false;
+ }
+ }
+ return out.toString();
+ }
+
+ private static boolean compareEqual(Object left, Object right) {
+ boolean leftBoolean = left instanceof Boolean;
+ boolean rightBoolean = right instanceof Boolean;
+ if (leftBoolean || rightBoolean) {
+ // Excel and POI treat booleans as strictly distinct from numbers and
strings: TRUE is not
+ // equal to 1, and FALSE is not equal to 0 or "Y".
+ if (leftBoolean != rightBoolean) {
+ return false;
+ }
+ return (Boolean) left == (Boolean) right;
+ }
+ boolean leftNumber = left instanceof Number;
+ boolean rightNumber = right instanceof Number;
+ if (leftNumber || rightNumber) {
+ // Excel and POI never compare a number with a string as equal, even
when they look the same.
+ if (leftNumber != rightNumber) {
+ return false;
+ }
+ return toNumber(left) == toNumber(right);
+ }
+ return TextValue.of(left).equalsIgnoreCase(TextValue.of(right));
+ }
+
+ /**
+ * Orders two operands the way Excel and POI do: booleans sort above text,
which sorts above
+ * numbers, and no operand is coerced across those types by stringifying it.
A blank operand
+ * compares as the other operand's type: 0 against a number, empty text
against text and FALSE
+ * against a boolean.
+ */
+ private static int compareOrdered(Object left, Object right) {
+ if (left == null || right == null) {
+ if (left == null && right == null) {
+ return 0;
+ }
+ Object other = left != null ? left : right;
+ boolean blankIsLeft = left == null;
+ int comparison;
+ if (other instanceof Boolean) {
+ comparison = (Boolean) other ? -1 : 0;
+ } else if (other instanceof Number) {
+ comparison = Double.compare(0.0d, ((Number) other).doubleValue());
+ } else {
+ comparison = ((String) other).isEmpty() ? 0 : -1;
+ }
+ return blankIsLeft ? comparison : -comparison;
+ }
+ if (left instanceof Boolean leftBoolean) {
+ if (right instanceof Boolean) {
+ return leftBoolean.compareTo((Boolean) right);
+ }
+ // Booleans rank above text and numbers.
+ return 1;
+ }
+ if (right instanceof Boolean) {
+ return -1;
+ }
+ if (left instanceof String leftText) {
+ if (right instanceof String) {
+ // Excel compares text case-insensitively.
+ return leftText.compareToIgnoreCase((String) right);
+ }
+ // Text ranks above numbers.
+ return 1;
+ }
+ if (right instanceof String) {
+ return -1;
+ }
+ return Double.compare(toNumber(left), toNumber(right));
+ }
+
+ /** The values that are coerced to a boolean, mirroring Excel's truthiness.
*/
+ static boolean toBoolean(Object value) {
+ if (value instanceof Boolean booleanValue) {
+ return booleanValue;
+ }
+ if (value instanceof Number number) {
+ return number.doubleValue() != 0.0d;
+ }
+ if (value == null) {
+ return false;
+ }
+ throw new UnsupportedFormulaException("Cannot use " + value + " as a
boolean");
+ }
+
+ /** The numeric value of an operand, throwing when it can not be treated as
a number. */
+ private static double toNumber(Object value) {
+ if (value instanceof Number number) {
+ return number.doubleValue();
+ }
+ if (value instanceof Boolean booleanValue) {
+ // POI BoolEval is numeric: TRUE is 1 and FALSE is 0, so arithmetic on
booleans works.
+ return booleanValue ? 1.0d : 0.0d;
+ }
+ if (value == null) {
+ // In Excel and POI a blank/null cell in an arithmetic expression is
treated as 0.
+ return 0.0d;
+ }
+ if (value == FastFormulaCompiler.NA) {
+ throw new UnsupportedFormulaException("Cannot use " + value + " as a
number");
+ }
+ if (value instanceof String string) {
+ try {
+ return Double.parseDouble(string.trim());
+ } catch (NumberFormatException e) {
+ throw new UnsupportedFormulaException("Cannot use \"" + string + "\"
as a number");
+ }
+ }
+ throw new UnsupportedFormulaException("Cannot use " + value + " as a
number");
+ }
+
+ /** Formats a number the way Excel shows it in a text concatenation. */
+ static String formatNumber(Object value) {
+ if (value instanceof Number number) {
+ double d = number.doubleValue();
+ if (d == Math.rint(d) && !Double.isInfinite(d) && Math.abs(d) <=
Long.MAX_VALUE) {
+ return Long.toString((long) d);
+ }
+ return Double.toString(d);
+ }
+ return String.valueOf(value);
+ }
+
+ /** Text rendering for concatenation, mirroring Excel's coercion of numeric
and boolean values. */
+ private static final class TextValue {
+ private static String of(Object value) {
+ if (value == null) {
+ return "";
+ }
+ if (value == FastFormulaCompiler.NA) {
+ // An error cell must not be rendered as a Java object hash; report it
as an error so the
+ // expression can not silently produce garbage like
"prefix-java.lang.Object@4f023fd2".
+ throw new UnsupportedFormulaException("Cannot render #N/A as text");
+ }
+ if (value instanceof Boolean booleanValue) {
+ return booleanValue ? "TRUE" : "FALSE";
+ }
+ if (value instanceof Number number) {
+ return formatNumber(number);
+ }
+ return String.valueOf(value);
+ }
+ }
+
+ /** A simple precedence-climbing parser over a char cursor. */
+ private static final class Parser {
+ private final String expression;
+ private final Map<String, Integer> fieldIndex;
+ private int pos;
+
+ private Parser(String expression, Map<String, Integer> fieldIndex) {
+ this.expression = expression;
+ this.fieldIndex = fieldIndex;
+ }
+
+ Node parseExpression() {
+ Node node = parseComparison();
+ skipWhitespace();
+ if (pos < expression.length()) {
+ throw new UnsupportedFormulaException(
+ "Unexpected trailing content at position " + pos + ": " +
expression.substring(pos));
+ }
+ return node;
+ }
+
+ private Node parseComparison() {
+ Node node = parseConcat();
+ skipWhitespace();
+ while (pos < expression.length()) {
+ BinaryOp op = matchComparison();
+ if (op == null) {
+ break;
+ }
+ Node right = parseConcat();
+ node = new BinaryNode(node, right, op);
+ skipWhitespace();
+ }
+ return node;
+ }
+
+ private Node parseConcat() {
+ Node node = parseAdditive();
+ while (true) {
+ skipWhitespace();
+ if (!(pos < expression.length() && expression.charAt(pos) == '&')) {
+ return node;
+ }
+ pos++;
+ Node right = parseAdditive();
+ node = new BinaryNode(node, right, BinaryOp.CONCAT);
+ }
+ }
+
+ private Node parseAdditive() {
+ Node node = parseMultiplicative();
+ skipWhitespace();
+ while (pos < expression.length()) {
+ char c = expression.charAt(pos);
+ if (c == '+') {
+ pos++;
+ node = new BinaryNode(node, parseMultiplicative(), BinaryOp.ADD);
+ } else if (c == '-') {
+ pos++;
+ node = new BinaryNode(node, parseMultiplicative(),
BinaryOp.SUBTRACT);
+ } else {
+ break;
+ }
+ skipWhitespace();
+ }
+ return node;
+ }
+
+ private Node parseMultiplicative() {
+ Node node = parseUnary();
+ skipWhitespace();
+ while (pos < expression.length()) {
+ char c = expression.charAt(pos);
+ if (c == '*') {
+ pos++;
+ node = new BinaryNode(node, parseUnary(), BinaryOp.MULTIPLY);
+ } else if (c == '/') {
+ pos++;
+ node = new BinaryNode(node, parseUnary(), BinaryOp.DIVIDE);
+ } else {
+ break;
+ }
+ skipWhitespace();
+ }
+ return node;
+ }
+
+ private Node parseUnary() {
+ skipWhitespace();
+ if (pos < expression.length() && expression.charAt(pos) == '-') {
+ pos++;
+ return new UnaryNode(parseUnary());
+ }
+ return parseAtom();
+ }
+
+ private Node parseAtom() {
+ skipWhitespace();
+ if (pos >= expression.length()) {
+ throw new UnsupportedFormulaException("Unexpected end of formula");
+ }
+ char c = expression.charAt(pos);
+ if (c == '(') {
+ pos++;
+ Node node = parseComparison();
+ skipWhitespace();
+ expect(')');
+ return node;
+ }
+ if (c == '[') {
+ return parseField();
+ }
+ if (c == '"' || c == '\'') {
+ return new LiteralNode(parseString(c));
+ }
+ if (c == '-' || (c >= '0' && c <= '9')) {
+ return new LiteralNode(parseNumber());
+ }
+ if (Character.isLetter(c) || c == '_') {
+ return parseIdentifier();
+ }
+ throw new UnsupportedFormulaException("Unexpected character '" + c + "'
at position " + pos);
+ }
+
+ private Node parseField() {
+ int start = pos + 1;
+ int end = expression.indexOf(']', start);
+ if (end < 0) {
+ throw new UnsupportedFormulaException("Unclosed field reference at
position " + pos);
+ }
+ String name = expression.substring(start, end);
+ pos = end + 1;
+ Integer index = fieldIndex.get(name);
+ if (index == null) {
+ throw new UnsupportedFormulaException("Unknown field [" + name + "]");
+ }
+ return new FieldNode(index);
+ }
+
+ private Node parseIdentifier() {
+ int start = pos;
+ while (pos < expression.length()) {
+ char c = expression.charAt(pos);
+ if (Character.isLetterOrDigit(c) || c == '_') {
+ pos++;
+ } else {
+ break;
+ }
+ }
+ String word = expression.substring(start, pos);
+ String upper = word.toUpperCase();
+ skipWhitespace();
+ if (pos < expression.length() && expression.charAt(pos) == '(') {
+ return parseFunction(upper);
+ }
+ if ("TRUE".equals(upper)) {
+ return new LiteralNode(Boolean.TRUE);
+ }
+ if ("FALSE".equals(upper)) {
+ return new LiteralNode(Boolean.FALSE);
+ }
+ throw new UnsupportedFormulaException("Unknown identifier " + word);
+ }
+
+ private Node parseFunction(String name) {
+ if (!SUPPORTED_FUNCTIONS.contains(name)) {
+ throw new UnsupportedFormulaException("Unsupported function " + name);
+ }
+ expect('(');
+ List<Node> arguments = new ArrayList<>();
+ skipWhitespace();
+ if (pos < expression.length() && expression.charAt(pos) == ')') {
+ pos++;
+ return new FunctionNode(name, arguments);
+ }
+ while (true) {
+ arguments.add(parseComparison());
+ skipWhitespace();
+ if (pos < expression.length() && expression.charAt(pos) == ',') {
+ pos++;
+ skipWhitespace();
+ continue;
+ }
+ expect(')');
+ break;
+ }
+ return new FunctionNode(name, arguments);
+ }
+
+ private double parseNumber() {
+ int start = pos;
+ boolean decimal = false;
+ boolean exponent = false;
+ while (pos < expression.length()) {
+ char c = expression.charAt(pos);
+ if (c >= '0' && c <= '9') {
+ pos++;
+ } else if (c == '.') {
+ if (decimal || exponent) {
+ break;
+ }
+ decimal = true;
+ pos++;
+ } else if ((c == 'e' || c == 'E')) {
+ if (exponent) {
+ break;
+ }
+ exponent = true;
+ pos++;
+ if (pos < expression.length()
+ && (expression.charAt(pos) == '+' || expression.charAt(pos) ==
'-')) {
+ pos++;
+ }
+ } else {
+ break;
+ }
+ }
+ if (pos == start) {
+ throw new UnsupportedFormulaException("Invalid number at position " +
pos);
+ }
+ return Double.parseDouble(expression.substring(start, pos));
+ }
+
+ private String parseString(char quote) {
+ pos++;
+ StringBuilder out = new StringBuilder();
+ while (pos < expression.length()) {
+ char c = expression.charAt(pos);
+ if (c == quote) {
+ if (pos + 1 < expression.length() && expression.charAt(pos + 1) ==
quote) {
+ out.append(quote);
+ pos += 2;
+ continue;
+ }
+ pos++;
+ return out.toString();
+ }
+ out.append(c);
+ pos++;
+ }
+ throw new UnsupportedFormulaException("Unclosed string literal");
+ }
+
+ private BinaryOp matchComparison() {
+ char c = expression.charAt(pos);
+ switch (c) {
+ case '=':
+ pos++;
+ return BinaryOp.EQUAL;
+ case '>':
+ pos++;
+ if (pos < expression.length() && expression.charAt(pos) == '=') {
+ pos++;
+ return BinaryOp.GREATER_OR_EQUAL;
+ }
+ return BinaryOp.GREATER;
+ case '<':
+ pos++;
+ if (pos < expression.length() && expression.charAt(pos) == '=') {
+ pos++;
+ return BinaryOp.LESS_OR_EQUAL;
+ }
+ if (pos < expression.length() && expression.charAt(pos) == '>') {
+ pos++;
+ return BinaryOp.NOT_EQUAL;
+ }
+ return BinaryOp.LESS;
+ default:
+ return null;
+ }
+ }
+
+ private void expect(char expected) {
+ skipWhitespace();
+ if (pos >= expression.length() || expression.charAt(pos) != expected) {
+ throw new UnsupportedFormulaException("Expected '" + expected + "' at
position " + pos);
+ }
+ pos++;
+ }
+
+ private void skipWhitespace() {
+ while (pos < expression.length() &&
Character.isWhitespace(expression.charAt(pos))) {
+ pos++;
+ }
+ }
+ }
+
+ private static final List<String> SUPPORTED_FUNCTIONS =
+ List.of("IF", "AND", "OR", "NOT", "ABS", "ISBLANK", "ISNA", "LEN",
"TRIM");
+
+ /** Signals that a formula can not be evaluated by the fast path. */
+ static final class UnsupportedFormulaException extends RuntimeException {
+ UnsupportedFormulaException(String message) {
+ super(message);
+ }
+ }
+}
diff --git
a/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/config/messages/messages_en_US.properties
b/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/config/messages/messages_en_US.properties
new file mode 100644
index 0000000000..fc8108e476
--- /dev/null
+++
b/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/config/messages/messages_en_US.properties
@@ -0,0 +1,23 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+#
+
+FormulaConfigPlugin.Name=Formula
+FormulaConfigPlugin.FastPathEnabled.Label=Fast formula evaluation
+FormulaConfigPlugin.FastPathEnabled.Tooltip=Evaluate the formulas that only
use the supported functions and operators without Apache POI, which is many
times faster. Formulas using anything else are always evaluated with Apache
POI. Clear this to evaluate every formula with Apache POI. A pipeline can also
switch it off with the variable HOP_FORMULA_FAST_PATH_ENABLED set to N.
+FormulaConfigPlugin.SavingOption.ErrorDialog.Header=Error
+FormulaConfigPlugin.SavingOption.ErrorDialog.Message=Error saving the Formula
transform options
diff --git
a/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/FormulaFastPathParityTest.java
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/FormulaFastPathParityTest.java
new file mode 100644
index 0000000000..4a790dcb09
--- /dev/null
+++
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/FormulaFastPathParityTest.java
@@ -0,0 +1,384 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.hop.pipeline.transforms.formula;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.Mockito.doNothing;
+import static org.mockito.Mockito.doReturn;
+import static org.mockito.Mockito.spy;
+import static org.mockito.Mockito.when;
+
+import java.math.BigDecimal;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+import org.apache.hop.core.config.HopConfig;
+import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.logging.ILoggingObject;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaBigNumber;
+import org.apache.hop.core.row.value.ValueMetaBoolean;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaNumber;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.junit.rules.RestoreHopEnvironmentExtension;
+import org.apache.hop.pipeline.transforms.formula.config.FormulaConfig;
+import org.apache.hop.pipeline.transforms.formula.fast.FastFormulaCompiler;
+import org.apache.hop.pipeline.transforms.mock.TransformMockHelper;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.ExtendWith;
+import org.mockito.ArgumentCaptor;
+
+/**
+ * Ensures the fast path produces exactly the same output rows as the regular
POI path for every
+ * formula the fast path supports. Each scenario is run once with the fast
path enabled and once
+ * with it disabled, and the captured output rows must be identical.
+ */
+@ExtendWith(RestoreHopEnvironmentExtension.class)
+class FormulaFastPathParityTest {
+ private TransformMockHelper<FormulaMeta, FormulaData> transformMockHelper;
+
+ /** The hop-config.json option as it was before the test, put back
afterwards. */
+ private Object savedFastPathOption;
+
+ @BeforeEach
+ void setUp() {
+ savedFastPathOption =
+
HopConfig.getInstance().getConfigMap().remove(FormulaConfig.OPTION_FAST_PATH_ENABLED);
+ FastFormulaCompiler.clear();
+ transformMockHelper =
+ new TransformMockHelper<>("Formula", FormulaMeta.class,
FormulaData.class);
+ when(transformMockHelper.logChannelFactory.create(any(),
any(ILoggingObject.class)))
+ .thenReturn(transformMockHelper.iLogChannel);
+ when(transformMockHelper.pipeline.isRunning()).thenReturn(true);
+ when(transformMockHelper.transformMeta.getName()).thenReturn("Formula");
+ }
+
+ @AfterEach
+ void tearDown() {
+ FastFormulaCompiler.clear();
+ if (savedFastPathOption == null) {
+
HopConfig.getInstance().getConfigMap().remove(FormulaConfig.OPTION_FAST_PATH_ENABLED);
+ } else {
+ HopConfig.getInstance()
+ .getConfigMap()
+ .put(FormulaConfig.OPTION_FAST_PATH_ENABLED, savedFastPathOption);
+ }
+ transformMockHelper.cleanUp();
+ }
+
+ private static final Scenario SUM =
+ new Scenario(
+ "[left] + [right]",
+ IValueMeta.TYPE_NUMBER,
+ "sum",
+ List.of(
+ new Field(new ValueMetaInteger("left"), 10L),
+ new Field(new ValueMetaInteger("right"), 20L)),
+ false);
+
+ @Test
+ void theFastPathIsOnWhenNothingSwitchesItOff() throws Exception {
+ run(SUM, null, true);
+ }
+
+ @Test
+ void theHopConfigOptionSwitchesTheFastPathOff() throws Exception {
+
HopConfig.getInstance().getConfigMap().put(FormulaConfig.OPTION_FAST_PATH_ENABLED,
false);
+
+ run(SUM, null, false);
+ // A pipeline can not switch it back on.
+ run(SUM, "Y", false);
+ }
+
+ private record Field(IValueMeta meta, Object value) {}
+
+ private record Scenario(
+ String formula, int targetType, String outputField, List<Field>
inputFields, boolean setNa) {}
+
+ @Test
+ void fastAndPoiProduceTheSameRowsForSupportedFormulas() throws Exception {
+ List<Scenario> scenarios =
+ List.of(
+ new Scenario(
+ "IF([score] >= 60, \"Pass\", \"Fail\")",
+ IValueMeta.TYPE_STRING,
+ "grade",
+ List.of(new Field(new ValueMetaInteger("score"), 85L)),
+ false),
+ new Scenario(
+ "IF([score] >= 60, \"Pass\", \"Fail\")",
+ IValueMeta.TYPE_STRING,
+ "grade",
+ List.of(new Field(new ValueMetaInteger("score"), 40L)),
+ false),
+ new Scenario(
+ "[left] + [right]",
+ IValueMeta.TYPE_NUMBER,
+ "sum",
+ List.of(
+ new Field(new ValueMetaInteger("left"), 10L),
+ new Field(new ValueMetaInteger("right"), 20L)),
+ false),
+ new Scenario(
+ "[left] + [right]",
+ IValueMeta.TYPE_INTEGER,
+ "sumInt",
+ List.of(
+ new Field(new ValueMetaInteger("left"), 10L),
+ new Field(new ValueMetaInteger("right"), 20L)),
+ false),
+ new Scenario(
+ "[qty] * [factor]",
+ IValueMeta.TYPE_BIGNUMBER,
+ "product",
+ List.of(
+ new Field(new ValueMetaBigNumber("qty"), new
BigDecimal("10.5")),
+ new Field(new ValueMetaNumber("factor"), 2.0)),
+ false),
+ new Scenario(
+ "[qty] > 100",
+ IValueMeta.TYPE_BOOLEAN,
+ "big",
+ List.of(new Field(new ValueMetaBigNumber("qty"), new
BigDecimal("200.5"))),
+ false),
+ new Scenario(
+ "[qty] > 100",
+ IValueMeta.TYPE_BOOLEAN,
+ "big",
+ List.of(new Field(new ValueMetaBigNumber("qty"), new
BigDecimal("20.5"))),
+ false),
+ new Scenario(
+ "[label] = \"active\"",
+ IValueMeta.TYPE_BOOLEAN,
+ "match",
+ List.of(new Field(new ValueMetaString("label"), "active")),
+ false),
+ new Scenario(
+ "TRIM([name])",
+ IValueMeta.TYPE_STRING,
+ "clean",
+ List.of(new Field(new ValueMetaString("name"), " a b ")),
+ false),
+ new Scenario(
+ "ISBLANK([comment])",
+ IValueMeta.TYPE_BOOLEAN,
+ "blank",
+ List.of(new Field(new ValueMetaString("comment"), null)),
+ false),
+ new Scenario(
+ "ISNA([comment])",
+ IValueMeta.TYPE_BOOLEAN,
+ "na",
+ List.of(new Field(new ValueMetaString("comment"), null)),
+ false),
+ new Scenario(
+ "ISNA([comment])",
+ IValueMeta.TYPE_BOOLEAN,
+ "naWithOption",
+ List.of(new Field(new ValueMetaString("comment"), null)),
+ true),
+ new Scenario(
+ "ABS([delta])",
+ IValueMeta.TYPE_NUMBER,
+ "abs",
+ List.of(new Field(new ValueMetaNumber("delta"), -5.0)),
+ false),
+ new Scenario(
+ "LEN([code])",
+ IValueMeta.TYPE_NUMBER,
+ "len",
+ List.of(new Field(new ValueMetaString("code"), "Hop ")),
+ false),
+ new Scenario(
+ "[prefix] & \"-\" & [num]",
+ IValueMeta.TYPE_STRING,
+ "id",
+ List.of(
+ new Field(new ValueMetaString("prefix"), "id"),
+ new Field(new ValueMetaInteger("num"), 7L)),
+ false),
+ new Scenario(
+ "[amount] * [factor]",
+ IValueMeta.TYPE_NUMBER,
+ "scaled",
+ List.of(
+ new Field(new ValueMetaInteger("amount"), null),
+ new Field(new ValueMetaNumber("factor"), 2.0)),
+ false),
+ new Scenario(
+ "IF([score] >= 60, \"Pass\")",
+ IValueMeta.TYPE_STRING,
+ "grade2",
+ List.of(new Field(new ValueMetaInteger("score"), 40L)),
+ false),
+ new Scenario(
+ "TRIM([name])",
+ IValueMeta.TYPE_STRING,
+ "cleanTabs",
+ List.of(new Field(new ValueMetaString("name"), "a\tb")),
+ false),
+ new Scenario(
+ "[amount] + 10",
+ IValueMeta.TYPE_NUMBER,
+ "naSum",
+ List.of(new Field(new ValueMetaInteger("amount"), null)),
+ true),
+ new Scenario(
+ "[prefix] & [comment]",
+ IValueMeta.TYPE_STRING,
+ "naConcat",
+ List.of(
+ new Field(new ValueMetaString("prefix"), "id-"),
+ new Field(new ValueMetaString("comment"), null)),
+ true),
+ new Scenario(
+ "IF(ISNA([comment]), \"missing\", [comment])",
+ IValueMeta.TYPE_STRING,
+ "naIf",
+ List.of(new Field(new ValueMetaString("comment"), null)),
+ true),
+ new Scenario(
+ "LEN([comment])",
+ IValueMeta.TYPE_NUMBER,
+ "naLen",
+ List.of(new Field(new ValueMetaString("comment"), null)),
+ true),
+ new Scenario(
+ "[flag] * 10",
+ IValueMeta.TYPE_NUMBER,
+ "scaledFlag",
+ List.of(new Field(new ValueMetaBoolean("flag"), Boolean.TRUE)),
+ false),
+ new Scenario(
+ "[num] > \"199\"",
+ IValueMeta.TYPE_BOOLEAN,
+ "mixedRanking",
+ List.of(new Field(new ValueMetaInteger("num"), 200L)),
+ false),
+ new Scenario(
+ "[flag] > \"Z\"",
+ IValueMeta.TYPE_BOOLEAN,
+ "boolRanking",
+ List.of(new Field(new ValueMetaBoolean("flag"), Boolean.TRUE)),
+ false),
+ new Scenario(
+ "[empty] > -1",
+ IValueMeta.TYPE_BOOLEAN,
+ "blankRanking",
+ List.of(new Field(new ValueMetaInteger("empty"), null)),
+ false));
+
+ for (Scenario scenario : scenarios) {
+ List<Object[]> fastRows = run(scenario, "Y", true);
+ List<Object[]> poiRows = run(scenario, "N", false);
+ assertRowsEqual(scenario, fastRows, poiRows);
+ }
+ }
+
+ private void assertRowsEqual(Scenario scenario, List<Object[]> fast,
List<Object[]> poi) {
+ assertEquals(
+ fast.size(),
+ poi.size(),
+ "row count differs for " + scenario.formula() + ": fast=" + fast + "
poi=" + poi);
+ for (int i = 0; i < fast.size(); i++) {
+ assertEquals(
+ Arrays.asList(fast.get(i)),
+ Arrays.asList(poi.get(i)),
+ "row " + i + " differs for " + scenario.formula());
+ }
+ }
+
+ /**
+ * Run one row through the transform.
+ *
+ * @param scenario the formula and the row
+ * @param fastPathVariable the value of {@link
FastFormulaCompiler#FAST_PATH_VARIABLE}, null to
+ * leave it unset
+ * @param expectFastPath whether the formula has to take the fast path
+ * @return the rows written
+ */
+ private List<Object[]> run(Scenario scenario, String fastPathVariable,
boolean expectFastPath)
+ throws HopException {
+ try {
+ FastFormulaCompiler.clear();
+
+ RowMeta inputMeta = new RowMeta();
+ for (Field field : scenario.inputFields()) {
+ inputMeta.addValueMeta(field.meta());
+ }
+
+ FormulaMeta meta = new FormulaMeta();
+ meta.getFormulas()
+ .add(
+ new FormulaMetaFunction(
+ scenario.outputField(),
+ scenario.formula(),
+ scenario.targetType(),
+ -1,
+ -1,
+ "",
+ scenario.setNa()));
+
+ Formula formula =
+ spy(
+ new Formula(
+ transformMockHelper.transformMeta,
+ meta,
+ new FormulaData(),
+ 0,
+ transformMockHelper.pipelineMeta,
+ transformMockHelper.pipeline));
+
when(transformMockHelper.transformMeta.isDoingErrorHandling()).thenReturn(false);
+ org.junit.jupiter.api.Assertions.assertTrue(
+ formula.init(), "init failed for " + scenario.formula());
+ if (fastPathVariable != null) {
+ formula.setVariable(FastFormulaCompiler.FAST_PATH_VARIABLE,
fastPathVariable);
+ }
+ formula.setInputRowMeta(inputMeta);
+
+ Object[] inputRow = new Object[scenario.inputFields().size()];
+ for (int i = 0; i < scenario.inputFields().size(); i++) {
+ inputRow[i] = scenario.inputFields().get(i).value();
+ }
+ doReturn(inputRow).doReturn(null).when(formula).getRow();
+
+ ArgumentCaptor<Object[]> rows = ArgumentCaptor.forClass(Object[].class);
+ doNothing().when(formula).putRow(any(IRowMeta.class), rows.capture());
+
+ org.junit.jupiter.api.Assertions.assertTrue(formula.processRow());
+ // A NOT_ELIGIBLE formula would make both legs use POI and pass
vacuously, so each scenario
+ // must prove the fast path was taken when it is enabled and not taken
when disabled.
+ org.junit.jupiter.api.Assertions.assertEquals(
+ expectFastPath,
+ formula.compiledFormula(0).fastPath(),
+ "fast path usage for " + scenario.formula());
+ formula.processRow();
+ formula.dispose();
+
+ List<Object[]> captured = new ArrayList<>(rows.getAllValues());
+ return captured;
+ } finally {
+ FastFormulaCompiler.clear();
+ }
+ }
+}
diff --git
a/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/config/FormulaConfigPluginTest.java
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/config/FormulaConfigPluginTest.java
new file mode 100644
index 0000000000..ecd21ad207
--- /dev/null
+++
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/config/FormulaConfigPluginTest.java
@@ -0,0 +1,91 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.formula.config;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.Mockito.mock;
+
+import java.util.HashMap;
+import java.util.Map;
+import org.apache.hop.core.config.HopConfig;
+import org.apache.hop.core.gui.plugin.GuiWidgetElement;
+import org.apache.hop.core.logging.ILogChannel;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+class FormulaConfigPluginTest {
+
+ private final ILogChannel log = mock(ILogChannel.class);
+
+ private Map<String, Object> savedConfig;
+ private boolean savedInMemory;
+
+ /** An unconfigured Hop that never writes the real hop-config.json. */
+ @BeforeEach
+ void unconfiguredHop() {
+ savedInMemory = HopConfig.isInMemoryMode();
+ HopConfig.setInMemoryMode(true);
+ savedConfig = new HashMap<>(HopConfig.getInstance().getConfigMap());
+ HopConfig.getInstance().getConfigMap().clear();
+ }
+
+ @AfterEach
+ void restoreConfiguration() {
+ HopConfig.getInstance().getConfigMap().clear();
+ HopConfig.getInstance().getConfigMap().putAll(savedConfig);
+ HopConfig.setInMemoryMode(savedInMemory);
+ }
+
+ /** The configuration reference documents the annotated default, so it has
to be the real one. */
+ @Test
+ void theDocumentedDefaultIsTheRealDefault() throws Exception {
+ GuiWidgetElement widget =
+ FormulaConfigPlugin.class
+ .getDeclaredField("fastPathEnabled")
+ .getAnnotation(GuiWidgetElement.class);
+
+ assertEquals(
+ Boolean.parseBoolean(widget.defaultValue()),
+ FormulaConfigPlugin.getInstance().getFastPathEnabled());
+ assertTrue(FormulaConfig.isFastPathEnabled());
+ }
+
+ @Test
+ void hopConfSwitchesTheFastPathOffAndOnAgain() throws Exception {
+ FormulaConfigPlugin plugin = new FormulaConfigPlugin();
+
+ plugin.setFastPathEnabled(false);
+ assertTrue(plugin.handleOption(log, null, null));
+ assertFalse(FormulaConfig.isFastPathEnabled());
+ assertFalse(FormulaConfigPlugin.getInstance().getFastPathEnabled());
+
+ plugin.setFastPathEnabled(true);
+ assertTrue(plugin.handleOption(log, null, null));
+ assertTrue(FormulaConfig.isFastPathEnabled());
+ }
+
+ @Test
+ void withoutTheOptionHopConfLeavesItAlone() throws Exception {
+ assertFalse(new FormulaConfigPlugin().handleOption(log, null, null));
+ assertFalse(
+
HopConfig.getInstance().getConfigMap().containsKey(FormulaConfig.OPTION_FAST_PATH_ENABLED));
+ }
+}
diff --git
a/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/fast/FastFormulaCompilerTest.java
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/fast/FastFormulaCompilerTest.java
new file mode 100644
index 0000000000..f854f04bc9
--- /dev/null
+++
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/fast/FastFormulaCompilerTest.java
@@ -0,0 +1,405 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.hop.pipeline.transforms.formula.fast;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import java.math.BigDecimal;
+import java.util.List;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaBigNumber;
+import org.apache.hop.core.row.value.ValueMetaBoolean;
+import org.apache.hop.core.row.value.ValueMetaDate;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaNumber;
+import org.apache.hop.core.row.value.ValueMetaString;
+import
org.apache.hop.pipeline.transforms.formula.fast.FastFormulaCompiler.CompiledFormula;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+/** Unit tests for {@link FastFormulaCompiler}: eligibility and plain-Java
evaluation. */
+class FastFormulaCompilerTest {
+
+ private RowMeta rowMeta;
+
+ @BeforeEach
+ void setUp() {
+ FastFormulaCompiler.clear();
+ rowMeta = new RowMeta();
+ }
+
+ @AfterEach
+ void tearDown() {
+ FastFormulaCompiler.clear();
+ }
+
+ private void add(IValueMeta meta) {
+ rowMeta.addValueMeta(meta);
+ }
+
+ private Object evaluate(String formula, List<String> fields, boolean setNa,
Object[] row) {
+ CompiledFormula compiled = FastFormulaCompiler.compile(formula, fields,
rowMeta, setNa);
+ assertTrue(compiled.fastPath(), "expected fast path for " + formula);
+ return compiled.function().apply(row);
+ }
+
+ @Test
+ void numericFieldsAreSummedAsDoubles() {
+ add(new ValueMetaInteger("left"));
+ add(new ValueMetaInteger("right"));
+ Object result =
+ evaluate("[left] + [right]", List.of("left", "right"), false, new
Object[] {10L, 20L});
+ assertEquals(30.0d, result);
+ }
+
+ @Test
+ void bigNumberFieldIsComparedNumerically() {
+ add(new ValueMetaBigNumber("qty"));
+ Object result =
+ evaluate("[qty] > 100", List.of("qty"), false, new Object[] {new
BigDecimal("200.5")});
+ assertEquals(Boolean.TRUE, result);
+ }
+
+ @Test
+ void textFieldEqualityIsCaseInsensitive() {
+ add(new ValueMetaString("label"));
+ Object result =
+ evaluate("[label] = \"Active\"", List.of("label"), false, new Object[]
{"active"});
+ assertEquals(Boolean.TRUE, result);
+ }
+
+ @Test
+ void booleanFieldComparesToTrue() {
+ add(new ValueMetaBoolean("on"));
+ Object result = evaluate("[on] = TRUE", List.of("on"), false, new Object[]
{Boolean.TRUE});
+ assertEquals(Boolean.TRUE, result);
+ }
+
+ @Test
+ void ifSelectsTheTakenBranch() {
+ add(new ValueMetaInteger("score"));
+ Object result =
+ evaluate(
+ "IF([score] >= 60, \"Pass\", \"Fail\")", List.of("score"), false,
new Object[] {85L});
+ assertEquals("Pass", result);
+ }
+
+ @Test
+ void andOrNotAreSupported() {
+ add(new ValueMetaInteger("a"));
+ add(new ValueMetaInteger("b"));
+ assertEquals(
+ Boolean.TRUE,
+ evaluate("AND([a] > 1, [b] > 1)", List.of("a", "b"), false, new
Object[] {2L, 3L}));
+ assertEquals(
+ Boolean.FALSE,
+ evaluate("AND([a] > 9, [b] > 1)", List.of("a", "b"), false, new
Object[] {2L, 3L}));
+ assertEquals(
+ Boolean.TRUE,
+ evaluate("OR([a] > 9, [b] > 1)", List.of("a", "b"), false, new
Object[] {2L, 3L}));
+ assertEquals(Boolean.FALSE, evaluate("NOT([a] > 1)", List.of("a"), false,
new Object[] {2L}));
+ }
+
+ @Test
+ void logicalOperatorSymbolsAreNotEligible() {
+ add(new ValueMetaInteger("a"));
+ add(new ValueMetaInteger("b"));
+ // Excel and POI have no &&, || or ! operators: accepting them made
formulas work on the fast
+ // path only, so they must fall back to POI where they fail the same way
with or without it.
+ assertFalse(
+ FastFormulaCompiler.compile("[a] > 1 && [b] > 1", List.of("a", "b"),
rowMeta, false)
+ .fastPath());
+ assertFalse(
+ FastFormulaCompiler.compile("[a] > 1 || [b] > 9", List.of("a", "b"),
rowMeta, false)
+ .fastPath());
+ assertFalse(FastFormulaCompiler.compile("![a] > 9", List.of("a"), rowMeta,
false).fastPath());
+ }
+
+ @Test
+ void booleanArithmeticMatchesPoiNumericBooleans() {
+ add(new ValueMetaBoolean("flag"));
+ assertEquals(
+ 10.0d, evaluate("[flag] * 10", List.of("flag"), false, new Object[]
{Boolean.TRUE}));
+ assertEquals(2.0d, evaluate("TRUE + 1", List.of("flag"), false, new
Object[] {Boolean.TRUE}));
+ assertEquals(-1.0d, evaluate("-[flag]", List.of("flag"), false, new
Object[] {Boolean.TRUE}));
+ }
+
+ @Test
+ void orderedComparisonsUsePoiTypeRanking() {
+ add(new ValueMetaInteger("num"));
+ add(new ValueMetaBoolean("flag"));
+ // A number never sorts above text: POI ranks text above numbers instead
of stringifying.
+ assertEquals(
+ Boolean.FALSE, evaluate("[num] > \"199\"", List.of("num"), false, new
Object[] {200L}));
+ // Booleans rank above text.
+ assertEquals(
+ Boolean.TRUE,
+ evaluate("[flag] > \"Z\"", List.of("flag"), false, new Object[]
{Boolean.TRUE}));
+ // A blank operand coerces to the other operand's type: 0 against a number.
+ assertEquals(Boolean.TRUE, evaluate("[num] > -1", List.of("num"), false,
new Object[] {null}));
+ }
+
+ @Test
+ void naPropagatesLikeAPoiErrorCell() {
+ add(new ValueMetaInteger("amount"));
+ add(new ValueMetaString("comment"));
+ Object na = FastFormulaCompiler.NA;
+ assertEquals(
+ FastFormulaCompiler.NA,
+ evaluate("[amount] + 10", List.of("amount"), true, new Object[] {na}));
+ assertEquals(
+ FastFormulaCompiler.NA,
+ evaluate("LEN([comment])", List.of("comment"), true, new Object[]
{na}));
+ assertEquals(
+ FastFormulaCompiler.NA,
+ evaluate("[comment] = \"x\"", List.of("comment"), true, new Object[]
{na}));
+ assertEquals(
+ FastFormulaCompiler.NA,
+ evaluate("IF([amount] > 1, \"big\")", List.of("amount"), true, new
Object[] {na}));
+ assertEquals(
+ "na",
+ evaluate(
+ "IF(ISNA([comment]), \"na\", \"ok\")", List.of("comment"), true,
new Object[] {na}));
+ }
+
+ @Test
+ void trimMatchesPoiWhitespaceSemantics() {
+ add(new ValueMetaString("name"));
+ // Only characters up to the ASCII space are trimmed at the ends and ASCII
spaces collapse:
+ // a tab inside the text must survive, exactly like POI's
trim().replaceAll(" +", " ").
+ assertEquals("a\tb", evaluate("TRIM([name])", List.of("name"), false, new
Object[] {"a\tb"}));
+ assertEquals("a b", evaluate("TRIM([name])", List.of("name"), false, new
Object[] {"\ta b "}));
+ }
+
+ @Test
+ void absLenAndTrimAreSupported() {
+ add(new ValueMetaNumber("delta"));
+ assertEquals(5.0d, evaluate("ABS([delta])", List.of("delta"), false, new
Object[] {-5.0}));
+ add(new ValueMetaString("name"));
+ assertEquals(4.0d, evaluate("LEN([name])", List.of("name"), false, new
Object[] {"Hop "}));
+ assertEquals(
+ "a b", evaluate("TRIM([name])", List.of("name"), false, new Object[]
{" a b "}));
+ }
+
+ @Test
+ void isBlankTrueForNullWithoutNaOption() {
+ add(new ValueMetaInteger("amount"));
+ Object result = evaluate("ISBLANK([amount])", List.of("amount"), false,
new Object[] {null});
+ assertEquals(Boolean.TRUE, result);
+ }
+
+ @Test
+ void isNaTrueForNullWithNaOption() {
+ add(new ValueMetaInteger("amount"));
+ Object result =
+ evaluate("ISNA([amount])", List.of("amount"), false, new Object[]
{FastFormulaCompiler.NA});
+ assertEquals(Boolean.TRUE, result);
+ }
+
+ @Test
+ void isNaFalseForBlankNull() {
+ add(new ValueMetaInteger("amount"));
+ Object result = evaluate("ISNA([amount])", List.of("amount"), false, new
Object[] {null});
+ assertEquals(Boolean.FALSE, result);
+ }
+
+ @Test
+ void concatCoercesValuesToText() {
+ add(new ValueMetaString("prefix"));
+ add(new ValueMetaInteger("num"));
+ Object result =
+ evaluate("[prefix] & [num]", List.of("prefix", "num"), false, new
Object[] {"id-", 7L});
+ assertEquals("id-7", result);
+ }
+
+ @Test
+ void everyComparisonOperatorIsSupported() {
+ add(new ValueMetaInteger("a"));
+ add(new ValueMetaInteger("b"));
+ assertEquals(
+ Boolean.TRUE, evaluate("[a] < [b]", List.of("a", "b"), false, new
Object[] {1L, 2L}));
+ assertEquals(
+ Boolean.TRUE, evaluate("[a] <= [b]", List.of("a", "b"), false, new
Object[] {2L, 2L}));
+ assertEquals(
+ Boolean.TRUE, evaluate("[a] >= [b]", List.of("a", "b"), false, new
Object[] {2L, 2L}));
+ assertEquals(
+ Boolean.TRUE, evaluate("[a] <> [b]", List.of("a", "b"), false, new
Object[] {1L, 2L}));
+ assertEquals(
+ Boolean.FALSE, evaluate("[a] <> [b]", List.of("a", "b"), false, new
Object[] {2L, 2L}));
+ }
+
+ @Test
+ void divisionByZeroThrowsDivZero() {
+ add(new ValueMetaInteger("a"));
+ add(new ValueMetaInteger("b"));
+ CompiledFormula compiled =
+ FastFormulaCompiler.compile("[a] / [b]", List.of("a", "b"), rowMeta,
false);
+ assertTrue(compiled.fastPath());
+ ArithmeticException e =
+ org.junit.jupiter.api.Assertions.assertThrows(
+ ArithmeticException.class, () -> compiled.function().apply(new
Object[] {1L, 0L}));
+ assertTrue(e.getMessage().contains("#DIV/0!"), e.getMessage());
+ }
+
+ @Test
+ void missingFieldIsNotEligibleInsteadOfCrashing() {
+ add(new ValueMetaInteger("present"));
+ CompiledFormula compiled =
+ FastFormulaCompiler.compile(
+ "[present] + [typo]", List.of("present", "typo"), rowMeta, false);
+ assertFalse(compiled.fastPath());
+ }
+
+ @Test
+ void nullInArithmeticIsTreatedAsZero() {
+ add(new ValueMetaInteger("amount"));
+ Object result = evaluate("[amount] + 10", List.of("amount"), false, new
Object[] {null});
+ assertEquals(10.0d, result);
+ }
+
+ @Test
+ void booleanAndNumberAreNeverEqual() {
+ add(new ValueMetaBoolean("on"));
+ assertEquals(
+ Boolean.FALSE, evaluate("[on] = 1", List.of("on"), false, new Object[]
{Boolean.TRUE}));
+ assertEquals(
+ Boolean.FALSE, evaluate("[on] = 0", List.of("on"), false, new Object[]
{Boolean.FALSE}));
+ }
+
+ @Test
+ void numberAndStringAreNeverEqual() {
+ add(new ValueMetaInteger("n"));
+ assertEquals(
+ Boolean.FALSE, evaluate("[n] = \"200\"", List.of("n"), false, new
Object[] {200L}));
+ }
+
+ @Test
+ void twoArgumentIfReturnsFalseWhenConditionIsFalse() {
+ add(new ValueMetaInteger("score"));
+ assertEquals(
+ Boolean.FALSE,
+ evaluate("IF([score] >= 60, \"Pass\")", List.of("score"), false, new
Object[] {40L}));
+ assertEquals(
+ "Pass",
+ evaluate("IF([score] >= 60, \"Pass\")", List.of("score"), false, new
Object[] {85L}));
+ }
+
+ @Test
+ void concatWithNaOptionYieldsNaInsteadOfRenderingHash() {
+ add(new ValueMetaString("prefix"));
+ add(new ValueMetaString("comment"));
+ Object result =
+ evaluate(
+ "[prefix] & \"-\" & [comment]",
+ List.of("prefix", "comment"),
+ false,
+ new Object[] {"pre", FastFormulaCompiler.NA});
+ assertEquals(FastFormulaCompiler.NA, result);
+ Object nullResult =
+ evaluate(
+ "[prefix] & \"-\" & [comment]",
+ List.of("prefix", "comment"),
+ false,
+ new Object[] {"pre", null});
+ assertEquals("pre-", nullResult);
+ }
+
+ @Test
+ void callerOptOutReportsNotEligible() {
+ add(new ValueMetaInteger("amount"));
+ CompiledFormula compiled =
+ FastFormulaCompiler.compile("[amount] + 1", List.of("amount"),
rowMeta, false, false);
+ assertFalse(compiled.fastPath());
+ }
+
+ @Test
+ void dateFieldIsNotEligible() {
+ add(new ValueMetaDate("start"));
+ CompiledFormula compiled =
+ FastFormulaCompiler.compile("[start]", List.of("start"), rowMeta,
false);
+ assertFalse(compiled.fastPath());
+ }
+
+ @Test
+ void ternaryIsNotEligible() {
+ add(new ValueMetaInteger("count"));
+ CompiledFormula compiled =
+ FastFormulaCompiler.compile("[count] != null ? 1 : 0",
List.of("count"), rowMeta, false);
+ assertFalse(compiled.fastPath());
+ }
+
+ @Test
+ void methodCallIsNotEligible() {
+ add(new ValueMetaString("code"));
+ CompiledFormula compiled =
+ FastFormulaCompiler.compile("[code].toLowerCase()", List.of("code"),
rowMeta, false);
+ assertFalse(compiled.fastPath());
+ }
+
+ @Test
+ void unsupportedFunctionIsNotEligible() {
+ add(new ValueMetaString("name"));
+ CompiledFormula compiled =
+ FastFormulaCompiler.compile("UPPER([name])", List.of("name"), rowMeta,
false);
+ assertFalse(compiled.fastPath());
+ }
+
+ @Test
+ void identicalFormulasShareOneCacheEntry() {
+ add(new ValueMetaInteger("amount"));
+ FastFormulaCompiler.compile("[amount] + 1", List.of("amount"), rowMeta,
false);
+ FastFormulaCompiler.compile("[amount] + 1", List.of("amount"), rowMeta,
false);
+ assertEquals(1, FastFormulaCompiler.cacheSize());
+ }
+
+ @Test
+ void cacheEntryCountsDistinctSchemas() {
+ add(new ValueMetaInteger("amount"));
+ FastFormulaCompiler.compile("[amount] + 1", List.of("amount"), rowMeta,
false);
+ RowMeta other = new RowMeta();
+ other.addValueMeta(new ValueMetaString("amount"));
+ FastFormulaCompiler.compile("[amount] + 1", List.of("amount"), other,
false);
+ assertEquals(2, FastFormulaCompiler.cacheSize());
+ }
+
+ @Test
+ void cacheEntryIsSharedAcrossSetNaOptions() {
+ add(new ValueMetaInteger("amount"));
+ FastFormulaCompiler.compile("[amount] + 1", List.of("amount"), rowMeta,
false);
+ FastFormulaCompiler.compile("[amount] + 1", List.of("amount"), rowMeta,
true);
+ // setNa only affects per-row argument building, not the compiled AST, so
it shares one entry.
+ assertEquals(1, FastFormulaCompiler.cacheSize());
+ }
+
+ @Test
+ void cacheEvictsLeastRecentlyUsedAboveMaxSize() {
+ add(new ValueMetaInteger("amount"));
+ int max = FastFormulaCompiler.DEFAULT_MAX_SIZE;
+ for (int i = 0; i < max; i++) {
+ FastFormulaCompiler.compile("[amount] + " + i, List.of("amount"),
rowMeta, false);
+ }
+ assertEquals(max, FastFormulaCompiler.cacheSize());
+ FastFormulaCompiler.compile("[amount] + " + max, List.of("amount"),
rowMeta, false);
+ assertTrue(
+ FastFormulaCompiler.cacheSize() <= max,
+ "cache should not grow past the configured max size");
+ }
+}
