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 732c5954a6 Formula transform hardening, fixes #3572 (#7916)
732c5954a6 is described below
commit 732c5954a6365cfd125df85007afeff2d0e1aaef
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Wed Aug 12 16:37:52 2026 +0200
Formula transform hardening, fixes #3572 (#7916)
---
.../ROOT/pages/pipeline/transforms/formula.adoc | 25 +-
.../transforms/0042-formula-error-values.hpl | 597 +++++++++++++++++++++
.../transforms/0042-formula-na-and-iferror.hpl | 267 +++++++++
.../0042-formula-pre1900-error-handling.hpl | 560 +++++++++++++++++++
.../transforms/0042-formula-pre1900-fails.hpl | 136 +++++
.../main-0042-formula-error-handling.hwf | 234 ++++++++
.../hop/pipeline/transforms/formula/Formula.java | 67 ++-
.../pipeline/transforms/formula/FormulaMeta.java | 7 +
.../transforms/formula/util/FormulaParser.java | 31 +-
.../transforms/formula/function/functions.xml | 4 +-
.../formula/messages/messages_en_US.properties | 3 +
.../formula/FormulaErrorHandlingTest.java | 176 ++++++
.../formula/util/FormulaParserEvaluationTest.java | 65 +++
tools/install-wave1-plugins.sh | 2 +-
14 files changed, 2166 insertions(+), 8 deletions(-)
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 119e167d3b..650dd970ff 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
@@ -80,6 +80,27 @@ TIP: The formula will not evaluate a field unless the field
pre-exists. Either s
* Set a Null Timestamp with formula: DATEVALUE("0")
+== Dates before 1900
+
+WARNING: The Formula transform can not calculate with dates before
*1899-12-31*. Formulas are evaluated the way Excel does, as date serial numbers
counting the days since that date, so there simply is no representation for
anything older.
+
+This has two consequences to be aware of.
+
+*Date and Timestamp fields older than 1899-12-31 are rejected.* Because such a
date can not be converted to a serial number, the transform reports an error
for the row instead of calculating with a wrong value. Applications that use a
very old date as a placeholder for "no value" (1750-01-01 and similar) will run
into this. Filter or convert those values before the Formula transform, for
example by mapping them to `null` in a Calculator, Switch/Case or Filter Rows
transform, or divert th [...]
+
+*The DATE() function adds 1900 to years below 1900.* This is Excel behavior,
not a Hop limitation: a year between 0 and 1899 is interpreted as an offset
from 1900. `DATE(1800,1,1)` therefore returns 3700-01-01 and `DATE(50,1,1)`
returns 1950-01-01. Hop can not detect this, since the result is a perfectly
valid date, so double check any formula that builds a date from a literal or
calculated year.
+
+== Error handling
+
+A formula that can not be calculated for a row stops the pipeline with an
error message naming the field and the formula. This covers unsupported dates
as described above, as well as the error values Excel returns for a failed
calculation: `#DIV/0!`, `#VALUE!`, `#NUM!`, `#REF!`, `#NAME?` and `#NULL!`.
+
+Two options are available to handle these rows instead:
+
+* Handle the error inside the formula with `IFERROR()`, for example
`IFERROR([amount]/[count], 0)`.
+* Enable error handling on the transform to divert the offending rows to
another transform, leaving the rest of the stream untouched.
+
+NOTE: `#N/A` is the exception: it means "no value available" and is written to
the output field as a `null`. This is what the *Set Null to #N/A* option relies
on, so formulas using `NA()` keep working as before.
+
== Options
[options="header"]
@@ -290,8 +311,8 @@ The kettle import process is able to migrate the existing
Formula steps containe
|*description*|Construct date from year, month, and day of month.
|*syntax*|DATE( Integer Year , Integer Month , Integer Day )
|*returns*|Date
-|*semantics*|This computes the date's serial number given Year, Month, and
Day. Fractional values are truncated. The value of the serial number depends on
the current epoch. Note that some applications may not handle correctly dates
before 1904; in particular, many spreadsheets incorrectly claim that 1900 is a
leap year (it was not; there was no 1900-02-29).
-|*constraints*|1 <= Month <= 12; 1 <= Day <= 31
+|*semantics*|This computes the date's serial number given Year, Month, and
Day. Fractional values are truncated. The value of the serial number depends on
the current epoch. Note that some applications may not handle correctly dates
before 1904; in particular, many spreadsheets incorrectly claim that 1900 is a
leap year (it was not; there was no 1900-02-29). A Year between 0 and 1899 is
interpreted as an offset from 1900, so DATE(1800,1,1) returns 3700-01-01 and
DATE(50,1,1) returns 1950 [...]
+|*constraints*|1 <= Month <= 12; 1 <= Day <= 31; Year >= 1900
|===
*Examples*
diff --git a/integration-tests/transforms/0042-formula-error-values.hpl
b/integration-tests/transforms/0042-formula-error-values.hpl
new file mode 100644
index 0000000000..5e93546ea5
--- /dev/null
+++ b/integration-tests/transforms/0042-formula-error-values.hpl
@@ -0,0 +1,597 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+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.
+
+-->
+<pipeline>
+ <info>
+ <name>0042-formula-error-values</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Excel reports a failed calculation with an error value
instead of throwing, which
+used to leave the output field silently blank. A #DIV/0! now fails the row, so
with error handling
+configured it is diverted to the error stream while the rows that do calculate
pass through.</description>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/08/12 09:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/08/12 09:00:00.000</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>input numbers</from>
+ <to>divide</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>divide</from>
+ <to>verify calculated rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>divide</from>
+ <to>verify error description</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify calculated rows</from>
+ <to>count calculated rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify calculated rows</from>
+ <to>Abort on wrong value</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>count calculated rows</from>
+ <to>verify calculated count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify calculated count</from>
+ <to>Calculated rows OK</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify calculated count</from>
+ <to>Abort on calculated count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify error description</from>
+ <to>count error rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify error description</from>
+ <to>Abort on wrong error</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>count error rows</from>
+ <to>verify error count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify error count</from>
+ <to>Errors OK</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify error count</from>
+ <to>Abort on error count</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>input numbers</name>
+ <type>DataGrid</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <data>
+ <line>
+ <item>div by zero</item>
+ <item>10</item>
+ <item>0</item>
+ </line>
+ <line>
+ <item>valid</item>
+ <item>10</item>
+ <item>2</item>
+ </line>
+ </data>
+ <fields>
+ <field>
+ <name>label</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ <field>
+ <name>numerator</name>
+ <type>Integer</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ <field>
+ <name>denominator</name>
+ <type>Integer</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>112</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>divide</name>
+ <type>Formula</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <formulas>
+ <formula>
+ <field_name>ratio</field_name>
+ <formula>[numerator]/[denominator]</formula>
+ <replace_field/>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>5</value_type>
+ </formula>
+ </formulas>
+ <attributes/>
+ <GUI>
+ <xloc>288</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>verify calculated rows</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>count calculated rows</send_true_to>
+ <send_false_to>Abort on wrong value</send_false_to>
+ <compare>
+ <condition>
+ <conditions>
+ <condition>
+ <conditions>
+</conditions>
+ <function>=</function>
+ <leftvalue>label</leftvalue>
+ <negated>N</negated>
+ <operator>-</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>valid</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ </value>
+ </condition>
+ <condition>
+ <conditions>
+</conditions>
+ <function>=</function>
+ <leftvalue>ratio</leftvalue>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>5</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ </conditions>
+ <negated>N</negated>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>480</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>count calculated rows</name>
+ <type>GroupBy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <all_rows>N</all_rows>
+ <ignore_aggregate>N</ignore_aggregate>
+ <field_ignore/>
+ <directory>${java.io.tmpdir}</directory>
+ <prefix>grp</prefix>
+ <add_linenr>N</add_linenr>
+ <linenr_fieldname/>
+ <give_back_row>N</give_back_row>
+ <group>
+ </group>
+ <fields>
+ <field>
+ <aggregate>calculated_rows</aggregate>
+ <subject>label</subject>
+ <type>COUNT_ALL</type>
+ <valuefield/>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>672</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>verify calculated count</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>Calculated rows OK</send_true_to>
+ <send_false_to>Abort on calculated count</send_false_to>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <leftvalue>calculated_rows</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>1</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>864</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Calculated rows OK</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>1056</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Abort on wrong value</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>672</xloc>
+ <yloc>16</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Abort on calculated count</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>1056</xloc>
+ <yloc>16</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>verify error description</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>count error rows</send_true_to>
+ <send_false_to>Abort on wrong error</send_false_to>
+ <compare>
+ <condition>
+ <conditions>
+ <condition>
+ <conditions>
+</conditions>
+ <function>CONTAINS</function>
+ <leftvalue>err_desc</leftvalue>
+ <negated>N</negated>
+ <operator>-</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>#DIV/0!</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ </value>
+ </condition>
+ <condition>
+ <conditions>
+</conditions>
+ <function>=</function>
+ <leftvalue>err_field</leftvalue>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>ratio</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ </value>
+ </condition>
+ <condition>
+ <conditions>
+</conditions>
+ <function>=</function>
+ <leftvalue>label</leftvalue>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>div by zero</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ </value>
+ </condition>
+ </conditions>
+ <negated>N</negated>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>480</xloc>
+ <yloc>240</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>count error rows</name>
+ <type>GroupBy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <all_rows>N</all_rows>
+ <ignore_aggregate>N</ignore_aggregate>
+ <field_ignore/>
+ <directory>${java.io.tmpdir}</directory>
+ <prefix>grp</prefix>
+ <add_linenr>N</add_linenr>
+ <linenr_fieldname/>
+ <give_back_row>N</give_back_row>
+ <group>
+ </group>
+ <fields>
+ <field>
+ <aggregate>error_rows</aggregate>
+ <subject>label</subject>
+ <type>COUNT_ALL</type>
+ <valuefield/>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>672</xloc>
+ <yloc>240</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>verify error count</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>Errors OK</send_true_to>
+ <send_false_to>Abort on error count</send_false_to>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <leftvalue>error_rows</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>1</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>864</xloc>
+ <yloc>240</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Errors OK</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>1056</xloc>
+ <yloc>240</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Abort on wrong error</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>672</xloc>
+ <yloc>320</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Abort on error count</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>1056</xloc>
+ <yloc>320</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ <error>
+ <source_transform>divide</source_transform>
+ <target_transform>verify error description</target_transform>
+ <is_enabled>Y</is_enabled>
+ <nr_valuename>err_nr</nr_valuename>
+ <descriptions_valuename>err_desc</descriptions_valuename>
+ <fields_valuename>err_field</fields_valuename>
+ <codes_valuename>err_code</codes_valuename>
+ <max_errors/>
+ <max_pct_errors/>
+ <min_pct_rows/>
+ </error>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/transforms/0042-formula-na-and-iferror.hpl
b/integration-tests/transforms/0042-formula-na-and-iferror.hpl
new file mode 100644
index 0000000000..816e0f2129
--- /dev/null
+++ b/integration-tests/transforms/0042-formula-na-and-iferror.hpl
@@ -0,0 +1,267 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+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.
+
+-->
+<pipeline>
+ <info>
+ <name>0042-formula-na-and-iferror</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Not every Excel error value is a failure. #N/A means "no
value available" and is
+produced on purpose by the "Set Null to #N/A" option, so it keeps mapping to a
null instead of
+failing the row. IFERROR() stays the in-formula way to handle the error values
that do fail.</description>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/08/12 09:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/08/12 09:00:00.000</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>generate 1 row</from>
+ <to>na and iferror formulas</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>na and iferror formulas</from>
+ <to>Verify</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Verify</from>
+ <to>Valid</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Verify</from>
+ <to>Invalid</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>generate 1 row</name>
+ <type>RowGenerator</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <fields>
+ <field>
+ <length>-1</length>
+ <name>one</name>
+ <nullif>1</nullif>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ <type>Integer</type>
+ </field>
+ </fields>
+ <interval_in_ms>5000</interval_in_ms>
+ <last_time_field>FiveSecondsAgo</last_time_field>
+ <limit>1</limit>
+ <never_ending>N</never_ending>
+ <row_time_field>now</row_time_field>
+ <attributes/>
+ <GUI>
+ <xloc>144</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>na and iferror formulas</name>
+ <type>Formula</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <formulas>
+ <formula>
+ <field_name>na_result</field_name>
+ <formula>NA()</formula>
+ <replace_field/>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>2</value_type>
+ </formula>
+ <formula>
+ <field_name>na_propagated</field_name>
+ <formula>SUM(NA(),1)</formula>
+ <replace_field/>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>5</value_type>
+ </formula>
+ <formula>
+ <field_name>isna_flag</field_name>
+ <formula>IF(ISNA(NA()), 1, 0)</formula>
+ <replace_field/>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>5</value_type>
+ </formula>
+ <formula>
+ <field_name>iferror_result</field_name>
+ <formula>IFERROR(1/0, -1)</formula>
+ <replace_field/>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>5</value_type>
+ </formula>
+ </formulas>
+ <attributes/>
+ <GUI>
+ <xloc>352</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Verify</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>Valid</send_true_to>
+ <send_false_to>Invalid</send_false_to>
+ <compare>
+ <condition>
+ <conditions>
+ <condition>
+ <conditions>
+</conditions>
+ <function>IS NULL</function>
+ <leftvalue>na_result</leftvalue>
+ <negated>N</negated>
+ <operator>-</operator>
+ <rightvalue/>
+ </condition>
+ <condition>
+ <conditions>
+</conditions>
+ <function>IS NULL</function>
+ <leftvalue>na_propagated</leftvalue>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <rightvalue/>
+ </condition>
+ <condition>
+ <conditions>
+</conditions>
+ <function>=</function>
+ <leftvalue>isna_flag</leftvalue>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>1</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ <condition>
+ <conditions>
+</conditions>
+ <function>=</function>
+ <leftvalue>iferror_result</leftvalue>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>-1</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ </conditions>
+ <negated>N</negated>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>560</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Valid</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>768</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Invalid</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>768</xloc>
+ <yloc>208</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git
a/integration-tests/transforms/0042-formula-pre1900-error-handling.hpl
b/integration-tests/transforms/0042-formula-pre1900-error-handling.hpl
new file mode 100644
index 0000000000..fbd3acfb0e
--- /dev/null
+++ b/integration-tests/transforms/0042-formula-pre1900-error-handling.hpl
@@ -0,0 +1,560 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+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.
+
+-->
+<pipeline>
+ <info>
+ <name>0042-formula-pre1900-error-handling</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>With error handling configured, a date the Formula transform
can not represent is
+diverted instead of stopping the pipeline. 1899-12-31 is the oldest supported
date (Excel date
+serial number 0), so only the 1800-01-01 row ends up on the error stream while
the two supported
+dates pass through unchanged. See
https://github.com/apache/hop/issues/3572</description>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/08/12 09:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/08/12 09:00:00.000</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>input dates</from>
+ <to>date formula</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>date formula</from>
+ <to>verify supported dates</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>date formula</from>
+ <to>verify error description</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify supported dates</from>
+ <to>count supported rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify supported dates</from>
+ <to>Abort on wrong date</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>count supported rows</from>
+ <to>verify supported count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify supported count</from>
+ <to>Supported dates OK</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify supported count</from>
+ <to>Abort on supported count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify error description</from>
+ <to>count error rows</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify error description</from>
+ <to>Abort on wrong error</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>count error rows</from>
+ <to>verify error count</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify error count</from>
+ <to>Errors OK</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>verify error count</from>
+ <to>Abort on error count</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>input dates</name>
+ <type>DataGrid</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <data>
+ <line>
+ <item>epoch</item>
+ <item>1899-12-31</item>
+ </line>
+ <line>
+ <item>supported</item>
+ <item>1900-01-01</item>
+ </line>
+ <line>
+ <item>too old</item>
+ <item>1800-01-01</item>
+ </line>
+ </data>
+ <fields>
+ <field>
+ <name>label</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ <field>
+ <name>input_date</name>
+ <type>Date</type>
+ <format>yyyy-MM-dd</format>
+ <length>-1</length>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>112</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>date formula</name>
+ <type>Formula</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <formulas>
+ <formula>
+ <field_name>passthrough_date</field_name>
+ <formula>IF(1=2, DATE(2000,1,1), [input_date])</formula>
+ <replace_field/>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>3</value_type>
+ </formula>
+ </formulas>
+ <attributes/>
+ <GUI>
+ <xloc>288</xloc>
+ <yloc>144</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>verify supported dates</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>count supported rows</send_true_to>
+ <send_false_to>Abort on wrong date</send_false_to>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <leftvalue>passthrough_date</leftvalue>
+ <function>=</function>
+ <rightvalue>input_date</rightvalue>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>480</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>count supported rows</name>
+ <type>GroupBy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <all_rows>N</all_rows>
+ <ignore_aggregate>N</ignore_aggregate>
+ <field_ignore/>
+ <directory>${java.io.tmpdir}</directory>
+ <prefix>grp</prefix>
+ <add_linenr>N</add_linenr>
+ <linenr_fieldname/>
+ <give_back_row>N</give_back_row>
+ <group>
+ </group>
+ <fields>
+ <field>
+ <aggregate>supported_rows</aggregate>
+ <subject>label</subject>
+ <type>COUNT_ALL</type>
+ <valuefield/>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>672</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>verify supported count</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>Supported dates OK</send_true_to>
+ <send_false_to>Abort on supported count</send_false_to>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <leftvalue>supported_rows</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>2</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>864</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Supported dates OK</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>1056</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Abort on wrong date</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>672</xloc>
+ <yloc>16</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Abort on supported count</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>1056</xloc>
+ <yloc>16</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>verify error description</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>count error rows</send_true_to>
+ <send_false_to>Abort on wrong error</send_false_to>
+ <compare>
+ <condition>
+ <conditions>
+ <condition>
+ <conditions>
+</conditions>
+ <function>CONTAINS</function>
+ <leftvalue>err_desc</leftvalue>
+ <negated>N</negated>
+ <operator>-</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>1899-12-31</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ </value>
+ </condition>
+ <condition>
+ <conditions>
+</conditions>
+ <function>=</function>
+ <leftvalue>err_field</leftvalue>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>passthrough_date</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ </value>
+ </condition>
+ <condition>
+ <conditions>
+</conditions>
+ <function>=</function>
+ <leftvalue>label</leftvalue>
+ <negated>N</negated>
+ <operator>AND</operator>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>String</type>
+ <text>too old</text>
+ <length>-1</length>
+ <precision>-1</precision>
+ <isnull>N</isnull>
+ </value>
+ </condition>
+ </conditions>
+ <negated>N</negated>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>480</xloc>
+ <yloc>240</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>count error rows</name>
+ <type>GroupBy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <all_rows>N</all_rows>
+ <ignore_aggregate>N</ignore_aggregate>
+ <field_ignore/>
+ <directory>${java.io.tmpdir}</directory>
+ <prefix>grp</prefix>
+ <add_linenr>N</add_linenr>
+ <linenr_fieldname/>
+ <give_back_row>N</give_back_row>
+ <group>
+ </group>
+ <fields>
+ <field>
+ <aggregate>error_rows</aggregate>
+ <subject>label</subject>
+ <type>COUNT_ALL</type>
+ <valuefield/>
+ </field>
+ </fields>
+ <attributes/>
+ <GUI>
+ <xloc>672</xloc>
+ <yloc>240</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>verify error count</name>
+ <type>FilterRows</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <send_true_to>Errors OK</send_true_to>
+ <send_false_to>Abort on error count</send_false_to>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <leftvalue>error_rows</leftvalue>
+ <function>=</function>
+ <rightvalue/>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>1</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ </condition>
+ </compare>
+ <attributes/>
+ <GUI>
+ <xloc>864</xloc>
+ <yloc>240</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Errors OK</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>1056</xloc>
+ <yloc>240</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Abort on wrong error</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>672</xloc>
+ <yloc>320</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Abort on error count</name>
+ <type>Abort</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <always_log_rows>Y</always_log_rows>
+ <row_threshold>0</row_threshold>
+ <attributes/>
+ <GUI>
+ <xloc>1056</xloc>
+ <yloc>320</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ <error>
+ <source_transform>date formula</source_transform>
+ <target_transform>verify error description</target_transform>
+ <is_enabled>Y</is_enabled>
+ <nr_valuename>err_nr</nr_valuename>
+ <descriptions_valuename>err_desc</descriptions_valuename>
+ <fields_valuename>err_field</fields_valuename>
+ <codes_valuename>err_code</codes_valuename>
+ <max_errors/>
+ <max_pct_errors/>
+ <min_pct_rows/>
+ </error>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/transforms/0042-formula-pre1900-fails.hpl
b/integration-tests/transforms/0042-formula-pre1900-fails.hpl
new file mode 100644
index 0000000000..181c1ec03f
--- /dev/null
+++ b/integration-tests/transforms/0042-formula-pre1900-fails.hpl
@@ -0,0 +1,136 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+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.
+
+-->
+<pipeline>
+ <info>
+ <name>0042-formula-pre1900-fails</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Formulas are evaluated with Excel date serial numbers, which
start at 1899-12-31.
+A date field older than that can not be represented, so the transform has to
report an error
+instead of silently turning the value into a blank or a wrong date. Without
error handling
+configured that error stops the pipeline, which is what this test expects: the
calling workflow
+treats a successful run as a failure. See
https://github.com/apache/hop/issues/3572</description>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2026/08/12 09:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/08/12 09:00:00.000</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>generate old date</from>
+ <to>formula on pre 1900 date</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>formula on pre 1900 date</from>
+ <to>Output</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>generate old date</name>
+ <type>RowGenerator</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <fields>
+ <field>
+ <format>yyyy-MM-dd</format>
+ <length>-1</length>
+ <name>old_date</name>
+ <nullif>1800-01-01</nullif>
+ <precision>-1</precision>
+ <set_empty_string>N</set_empty_string>
+ <type>Date</type>
+ </field>
+ </fields>
+ <interval_in_ms>5000</interval_in_ms>
+ <last_time_field>FiveSecondsAgo</last_time_field>
+ <limit>1</limit>
+ <never_ending>N</never_ending>
+ <row_time_field>now</row_time_field>
+ <attributes/>
+ <GUI>
+ <xloc>144</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>formula on pre 1900 date</name>
+ <type>Formula</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <formulas>
+ <formula>
+ <field_name>passthrough_date</field_name>
+ <formula>IF(1=2, DATE(2000,1,1), [old_date])</formula>
+ <replace_field/>
+ <value_length>-1</value_length>
+ <value_precision>-1</value_precision>
+ <value_type>3</value_type>
+ </formula>
+ </formulas>
+ <attributes/>
+ <GUI>
+ <xloc>352</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Output</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>560</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/transforms/main-0042-formula-error-handling.hwf
b/integration-tests/transforms/main-0042-formula-error-handling.hwf
new file mode 100644
index 0000000000..c7df329d4a
--- /dev/null
+++ b/integration-tests/transforms/main-0042-formula-error-handling.hwf
@@ -0,0 +1,234 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+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.
+
+-->
+<workflow>
+ <name>main-0042-formula-error-handling</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Formula transform error reporting: dates the Excel date serial
numbers can not
+represent, Excel error values, and the #N/A value that has to keep mapping to
a null.
+See https://github.com/apache/hop/issues/3572</description>
+ <extended_description/>
+ <created_user>-</created_user>
+ <modified_user>-</modified_user>
+ <created_date>2026/08/12 09:00:00.000</created_date>
+ <modified_date>2026/08/12 09:00:00.000</modified_date>
+ <workflow_version/>
+ <parameters/>
+ <actions>
+ <action>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <intervalSeconds>0</intervalSeconds>
+ <intervalMinutes>60</intervalMinutes>
+ <DayOfMonth>1</DayOfMonth>
+ <weekDay>1</weekDay>
+ <minutes>0</minutes>
+ <hour>12</hour>
+ <doNotWaitOnFirstExecution>N</doNotWaitOnFirstExecution>
+ <name>Start</name>
+ <description/>
+ <type>SPECIAL</type>
+ <attributes/>
+ <xloc>48</xloc>
+ <yloc>48</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+ <filename>${PROJECT_HOME}/0042-formula-na-and-iferror.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <create_parent_folder>N</create_parent_folder>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <run_configuration>local</run_configuration>
+ <name>0042-formula-na-and-iferror.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <xloc>208</xloc>
+ <yloc>48</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+ <filename>${PROJECT_HOME}/0042-formula-error-values.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <create_parent_folder>N</create_parent_folder>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <run_configuration>local</run_configuration>
+ <name>0042-formula-error-values.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <xloc>480</xloc>
+ <yloc>48</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+
<filename>${PROJECT_HOME}/0042-formula-pre1900-error-handling.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <create_parent_folder>N</create_parent_folder>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <run_configuration>local</run_configuration>
+ <name>0042-formula-pre1900-error-handling.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <xloc>784</xloc>
+ <yloc>48</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+ <filename>${PROJECT_HOME}/0042-formula-pre1900-fails.hpl</filename>
+ <params_from_previous>N</params_from_previous>
+ <exec_per_row>N</exec_per_row>
+ <clear_rows>N</clear_rows>
+ <clear_files>N</clear_files>
+ <create_parent_folder>N</create_parent_folder>
+ <set_logfile>N</set_logfile>
+ <set_append_logfile>N</set_append_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <wait_until_finished>Y</wait_until_finished>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <run_configuration>local</run_configuration>
+ <name>0042-formula-pre1900-fails.hpl</name>
+ <description>This pipeline is expected to FAIL: without error handling a
date the transform
+can not represent stops the pipeline. The hops are inverted
accordingly.</description>
+ <type>PIPELINE</type>
+ <attributes/>
+ <xloc>1072</xloc>
+ <yloc>48</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Success</name>
+ <description/>
+ <type>SUCCESS</type>
+ <attributes/>
+ <xloc>1280</xloc>
+ <yloc>144</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ <action>
+ <loglevel>Error</loglevel>
+ <name>Abort workflow</name>
+ <description/>
+ <type>ABORT</type>
+ <attributes/>
+ <xloc>1280</xloc>
+ <yloc>48</yloc>
+ <parallel>N</parallel>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>Start</from>
+ <to>0042-formula-na-and-iferror.hpl</to>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>0042-formula-na-and-iferror.hpl</from>
+ <to>0042-formula-error-values.hpl</to>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>0042-formula-error-values.hpl</from>
+ <to>0042-formula-pre1900-error-handling.hpl</to>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>0042-formula-pre1900-error-handling.hpl</from>
+ <to>0042-formula-pre1900-fails.hpl</to>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>0042-formula-pre1900-fails.hpl</from>
+ <to>Abort workflow</to>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>0042-formula-pre1900-fails.hpl</from>
+ <to>Success</to>
+ <evaluation>N</evaluation>
+ <unconditional>N</unconditional>
+ <enabled>Y</enabled>
+ </hop>
+ </hops>
+ <notepads/>
+ <attributes/>
+</workflow>
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 938861aa25..60a0e90bbd 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
@@ -27,10 +27,12 @@ import java.util.stream.IntStream;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.exception.HopRuntimeException;
import org.apache.hop.core.exception.HopTransformException;
+import org.apache.hop.core.exception.HopValueException;
import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.row.RowDataUtil;
import org.apache.hop.core.row.value.ValueMetaFactory;
import org.apache.hop.core.util.Utils;
+import org.apache.hop.i18n.BaseMessages;
import org.apache.hop.pipeline.Pipeline;
import org.apache.hop.pipeline.PipelineMeta;
import org.apache.hop.pipeline.transform.BaseTransform;
@@ -39,8 +41,10 @@ import
org.apache.hop.pipeline.transforms.formula.util.FormulaParser;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
+import org.apache.poi.ss.usermodel.FormulaError;
public class Formula extends BaseTransform<FormulaMeta, FormulaData> {
+ private static final Class<?> PKG = Formula.class; // for i18n purposes
private FormulaPoi[] poi;
private List<String>[] formulaFieldLists;
@@ -201,6 +205,9 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
data.returnType[i] = FormulaData.RETURN_TYPE_STRING;
formula.setNeedDataConversion(outputValueType !=
IValueMeta.TYPE_STRING);
break;
+ case ERROR:
+ outputValue = getErrorValue(cellValue, formula);
+ break;
default:
break;
}
@@ -210,8 +217,9 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
outputRowData[realIndex] =
getReturnValue(outputValue, data.returnType[i], realIndex,
formula);
} catch (Exception e) {
- throw new HopException(
- "Formula '" + formula.getFormula() + "' could not not be parsed ",
e);
+ // The row can not be calculated: divert it to the error stream if the
user asked for it,
+ // stop the pipeline otherwise.
+ return handleFormulaError(r, formula, e);
}
}
@@ -250,6 +258,61 @@ public class Formula extends BaseTransform<FormulaMeta,
FormulaData> {
super(transformMeta, meta, data, copyNr, pipelineMeta, pipeline);
}
+ /**
+ * Excel reports a failed calculation as an error value rather than by
throwing. Only {@code #N/A}
+ * means "no value available" - the transform produces it on purpose through
the "Set Null to
+ * #N/A" option - so it maps back to null. Every other error code ({@code
#DIV/0!}, {@code
+ * #VALUE!}, {@code #NUM!}, ...) is a genuine calculation failure and has to
be reported instead
+ * of silently turning the field blank.
+ *
+ * @param cellValue the evaluated cell holding the error
+ * @param formula the formula that produced it
+ * @return null for {@code #N/A}
+ * @throws HopValueException for any other error value
+ */
+ private Object getErrorValue(CellValue cellValue, FormulaMetaFunction
formula)
+ throws HopValueException {
+ byte errorCode = cellValue.getErrorValue();
+ if (FormulaError.isValidCode(errorCode) && FormulaError.forInt(errorCode)
== FormulaError.NA) {
+ return null;
+ }
+ String errorText =
+ FormulaError.isValidCode(errorCode)
+ ? FormulaError.forInt(errorCode).getString()
+ : Byte.toString(errorCode);
+ throw new HopValueException(
+ BaseMessages.getString(
+ PKG, "Formula.Exception.FormulaError", formula.getFieldName(),
errorText));
+ }
+
+ /**
+ * A formula could not be calculated for the current row. Send the row to
the error stream when
+ * error handling is configured, stop the pipeline otherwise.
+ *
+ * @param row the input row that could not be calculated
+ * @param formula the formula that failed
+ * @param e the cause of the failure
+ * @return true when the row was diverted and processing can continue, false
to end this transform
+ * @throws HopTransformException when the row could not be written to the
error stream
+ */
+ private boolean handleFormulaError(Object[] row, FormulaMetaFunction
formula, Exception e)
+ throws HopTransformException {
+ String message =
+ BaseMessages.getString(
+ PKG, "Formula.Exception.CouldNotBeEvaluated",
formula.getFormula(), e.getMessage());
+
+ if (getTransformMeta().isDoingErrorHandling()) {
+ putError(getInputRowMeta(), row, 1, message, formula.getFieldName(),
"Formula001");
+ return true;
+ }
+
+ logError(message, e);
+ setErrors(1);
+ stopAll();
+ setOutputDone();
+ return false;
+ }
+
protected Object getReturnValue(
Object formulaResult, int returnType, int realIndex, FormulaMetaFunction
fn)
throws HopException {
diff --git
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/FormulaMeta.java
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/FormulaMeta.java
index a901f297cd..5129c6ac1d 100644
---
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/FormulaMeta.java
+++
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/FormulaMeta.java
@@ -70,6 +70,13 @@ public class FormulaMeta extends BaseTransformMeta<Formula,
FormulaData> {
return new FormulaMeta(this);
}
+ @Override
+ public boolean supportsErrorHandling() {
+ // Rows for which a formula can not be calculated can be diverted instead
of stopping the
+ // pipeline.
+ return true;
+ }
+
@Override
public void getFields(
IRowMeta row,
diff --git
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParser.java
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParser.java
index a111f3bf49..a704f47aef 100644
---
a/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParser.java
+++
b/plugins/transforms/formula/src/main/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParser.java
@@ -19,6 +19,7 @@ package org.apache.hop.pipeline.transforms.formula.util;
import static
org.apache.hop.pipeline.transforms.formula.util.FormulaFieldsExtractor.getFormulaFieldList;
+import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Set;
@@ -26,14 +27,18 @@ import org.apache.hop.core.exception.HopValueException;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.IValueMeta;
import org.apache.hop.core.variables.IVariables;
+import org.apache.hop.i18n.BaseMessages;
+import org.apache.hop.pipeline.transforms.formula.Formula;
import org.apache.hop.pipeline.transforms.formula.FormulaMetaFunction;
import org.apache.hop.pipeline.transforms.formula.FormulaPoi;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
+import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellReference;
public class FormulaParser {
+ private static final Class<?> PKG = Formula.class; // for i18n purposes
private FormulaMetaFunction formulaMetaFunction;
private IRowMeta rowMeta;
@@ -116,7 +121,9 @@ public class FormulaParser {
} else if (fieldMeta.isBigNumber()) {
cell.setCellValue(rowMeta.getNumber(dataRow, fieldPosition));
} else if (fieldMeta.isDate()) {
- cell.setCellValue(rowMeta.getDate(dataRow, fieldPosition));
+ Date date = rowMeta.getDate(dataRow, fieldPosition);
+ checkSupportedDate(fieldMeta, date);
+ cell.setCellValue(date);
} else if (fieldMeta.isInteger()) {
cell.setCellValue(rowMeta.getInteger(dataRow, fieldPosition));
} else if (fieldMeta.isNumber()) {
@@ -145,4 +152,26 @@ public class FormulaParser {
return evaluator.evaluator().evaluate(formulaCell);
}
+
+ /**
+ * Formulas are evaluated as Excel date serial numbers, which start at
1899-12-31 (serial 0). POI
+ * silently maps anything older to the same BAD_DATE sentinel (-1), so every
earlier date would
+ * collapse to one value and come out of the transform blank or, after date
arithmetic, plain
+ * wrong. Report it instead of losing the value without a trace.
+ *
+ * @param fieldMeta the metadata of the date field being written to a cell
+ * @param date the value to write, may be null
+ * @throws HopValueException when the date can not be represented as an
Excel date serial number
+ */
+ private void checkSupportedDate(IValueMeta fieldMeta, Date date) throws
HopValueException {
+ if (date == null || DateUtil.getExcelDate(date) >= 0) {
+ return;
+ }
+ throw new HopValueException(
+ BaseMessages.getString(
+ PKG,
+ "Formula.Exception.DateBeforeExcelEpoch",
+ fieldMeta.getName(),
+ fieldMeta.getString(date)));
+ }
}
diff --git
a/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/function/functions.xml
b/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/function/functions.xml
index 39a4f8e2da..b06c970868 100644
---
a/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/function/functions.xml
+++
b/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/function/functions.xml
@@ -658,8 +658,8 @@
<description>Construct date from year, month, and day of
month.</description>
<syntax>DATE( Integer Year , Integer Month , Integer Day )</syntax>
<returns>Date</returns>
- <constraints>1 <= Month <= 12, 1 <= Day <= 31</constraints>
- <semantics>This computes the date's serial number given Year, Month, and
Day. Fractional values are truncated. The value of the serial number depends on
the current epoch. Note that some applications may not handle correctly dates
before 1904; in particular, many spreadsheets incorrectly claim that 1900 is a
leap year (it was not; there was no 1900-02-29).</semantics>
+ <constraints>1 <= Month <= 12, 1 <= Day <= 31, Year >=
1900</constraints>
+ <semantics>This computes the date's serial number given Year, Month, and
Day. Fractional values are truncated. The value of the serial number depends on
the current epoch. Note that some applications may not handle correctly dates
before 1904; in particular, many spreadsheets incorrectly claim that 1900 is a
leap year (it was not; there was no 1900-02-29). A Year between 0 and 1899 is
interpreted as an offset from 1900, so DATE(1800,1,1) returns 3700-01-01 and
DATE(50,1,1) returns 19 [...]
<examples>
<example><expression>DATE(2005,1,31)=[.C7]</expression>
<result>True</result> <level>1</level> <comment>Simple date
value.</comment></example>
<example><expression>DATE(2005,12,31)-DATE(1904,1,1)</expression>
<result>37255</result> <level>1</level> <comment>Date differences are computed
correctly.</comment></example>
diff --git
a/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/messages/messages_en_US.properties
b/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/messages/messages_en_US.properties
index 8ae1d5e1d5..85d47375b9 100644
---
a/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/messages/messages_en_US.properties
+++
b/plugins/transforms/formula/src/main/resources/org/apache/hop/pipeline/transforms/formula/messages/messages_en_US.properties
@@ -26,6 +26,9 @@ Category.Logical = Logical
Category.Mathematical = Mathematical
Category.Text = Text
Formula.description=Formula
+Formula.Exception.CouldNotBeEvaluated=Formula ''{0}'' could not be evaluated:
{1}
+Formula.Exception.DateBeforeExcelEpoch=Field [{0}] contains date {1}, which is
before 1899-12-31. Formulas are calculated with Excel date serial numbers,
which can not represent earlier dates.
+Formula.Exception.FormulaError=The formula for field [{0}] evaluated to {1}
Formula.keywords=formula,calculate,expression,math,libreoffice
Formula.name=Formula
FormulaDialog.DialogTitle = Formula
diff --git
a/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/FormulaErrorHandlingTest.java
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/FormulaErrorHandlingTest.java
new file mode 100644
index 0000000000..6c63342a7d
--- /dev/null
+++
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/FormulaErrorHandlingTest.java
@@ -0,0 +1,176 @@
+/*
+ * 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.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.anyLong;
+import static org.mockito.ArgumentMatchers.anyString;
+import static org.mockito.Mockito.doNothing;
+import static org.mockito.Mockito.doReturn;
+import static org.mockito.Mockito.never;
+import static org.mockito.Mockito.spy;
+import static org.mockito.Mockito.times;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
+
+import java.time.LocalDate;
+import java.time.ZoneId;
+import java.util.Date;
+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.ValueMetaDate;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.junit.rules.RestoreHopEnvironmentExtension;
+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;
+
+/**
+ * Unit tests for the way {@link Formula} reports formulas that can not be
calculated. Excel returns
+ * an error value instead of throwing, which used to leave the output field
silently blank.
+ */
+@ExtendWith(RestoreHopEnvironmentExtension.class)
+class FormulaErrorHandlingTest {
+ private TransformMockHelper<FormulaMeta, FormulaData> transformMockHelper;
+
+ @BeforeEach
+ void setUp() {
+ 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() {
+ transformMockHelper.cleanUp();
+ }
+
+ @Test
+ void naIsReturnedAsNull() throws Exception {
+ // #N/A means "no value available" and is produced on purpose by the "Set
Null to #N/A"
+ // option, so it has to stay a null rather than fail the row.
+ Formula formula = createTransform("NA()", IValueMeta.TYPE_NUMBER);
+ ArgumentCaptor<Object[]> rows = ArgumentCaptor.forClass(Object[].class);
+ doNothing().when(formula).putRow(any(IRowMeta.class), rows.capture());
+
+ assertTrue(formula.processRow());
+
+ assertNull(rows.getValue()[1]);
+ verify(formula, never()).putError(any(), any(), anyLong(), anyString(),
any(), anyString());
+ formula.dispose();
+ }
+
+ @Test
+ void divisionByZeroStopsThePipelineWithoutErrorHandling() throws Exception {
+ Formula formula = createTransform("1/0", IValueMeta.TYPE_NUMBER);
+
when(transformMockHelper.transformMeta.isDoingErrorHandling()).thenReturn(false);
+
+ assertFalse(formula.processRow());
+
+ assertEquals(1, formula.getErrors());
+ verify(formula, never()).putRow(any(), any());
+ verify(formula, never()).putError(any(), any(), anyLong(), anyString(),
any(), anyString());
+ formula.dispose();
+ }
+
+ @Test
+ void divisionByZeroGoesToTheErrorStreamWithErrorHandling() throws Exception {
+ Formula formula = createTransform("1/0", IValueMeta.TYPE_NUMBER);
+
when(transformMockHelper.transformMeta.isDoingErrorHandling()).thenReturn(true);
+ doNothing().when(formula).putError(any(), any(), anyLong(), anyString(),
any(), anyString());
+
+ // the row is diverted, the transform keeps going
+ assertTrue(formula.processRow());
+
+ ArgumentCaptor<String> description = ArgumentCaptor.forClass(String.class);
+ verify(formula, times(1))
+ .putError(any(), any(), anyLong(), description.capture(), any(),
anyString());
+ assertTrue(description.getValue().contains("#DIV/0!"),
description.getValue());
+ verify(formula, never()).putRow(any(), any());
+ formula.dispose();
+ }
+
+ @Test
+ void dateBeforeTheExcelEpochGoesToTheErrorStream() throws Exception {
+ // Before this was reported, the pre-1900 date silently became POI's
BAD_DATE and the field
+ // came out blank. See https://github.com/apache/hop/issues/3572
+ Date tooOld =
+ Date.from(LocalDate.of(1800, 1,
1).atStartOfDay(ZoneId.systemDefault()).toInstant());
+ Formula formula =
+ createTransform(
+ "IF(1=2, DATE(2000,1,1), [start])",
+ IValueMeta.TYPE_DATE,
+ new ValueMetaDate("start"),
+ tooOld);
+
when(transformMockHelper.transformMeta.isDoingErrorHandling()).thenReturn(true);
+ doNothing().when(formula).putError(any(), any(), anyLong(), anyString(),
any(), anyString());
+
+ assertTrue(formula.processRow());
+
+ ArgumentCaptor<String> description = ArgumentCaptor.forClass(String.class);
+ verify(formula, times(1))
+ .putError(any(), any(), anyLong(), description.capture(), any(),
anyString());
+ assertTrue(description.getValue().contains("1899-12-31"),
description.getValue());
+ verify(formula, never()).putRow(any(), any());
+ formula.dispose();
+ }
+
+ private Formula createTransform(String expression, int valueType) throws
HopException {
+ return createTransform(expression, valueType, new
ValueMetaInteger("amount"), 10L);
+ }
+
+ /** Builds a Formula transform with a single formula, fed a single row
holding one field. */
+ private Formula createTransform(
+ String expression, int valueType, IValueMeta inputMeta, Object
inputValue)
+ throws HopException {
+ FormulaMeta meta = new FormulaMeta();
+ meta.getFormulas()
+ .add(new FormulaMetaFunction("result", expression, valueType, -1, -1,
"", false));
+
+ Formula formula =
+ spy(
+ new Formula(
+ transformMockHelper.transformMeta,
+ meta,
+ new FormulaData(),
+ 0,
+ transformMockHelper.pipelineMeta,
+ transformMockHelper.pipeline));
+ assertTrue(formula.init());
+
+ RowMeta inputRowMeta = new RowMeta();
+ inputRowMeta.addValueMeta(inputMeta);
+ formula.setInputRowMeta(inputRowMeta);
+
+ doReturn(new Object[] {inputValue}).when(formula).getRow();
+ return formula;
+ }
+}
diff --git
a/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParserEvaluationTest.java
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParserEvaluationTest.java
index aa85a431d1..7b0c0817ef 100644
---
a/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParserEvaluationTest.java
+++
b/plugins/transforms/formula/src/test/java/org/apache/hop/pipeline/transforms/formula/util/FormulaParserEvaluationTest.java
@@ -19,18 +19,26 @@ package org.apache.hop.pipeline.transforms.formula.util;
import static
org.apache.hop.pipeline.transforms.formula.util.FormulaFieldsExtractor.getFormulaFieldList;
import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
import java.math.BigDecimal;
+import java.sql.Timestamp;
+import java.time.LocalDate;
+import java.time.ZoneId;
+import java.util.Date;
import java.util.HashMap;
import java.util.List;
+import org.apache.hop.core.exception.HopValueException;
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.core.row.value.ValueMetaTimestamp;
import org.apache.hop.core.variables.Variables;
import org.apache.hop.pipeline.transforms.formula.FormulaMetaFunction;
import org.apache.hop.pipeline.transforms.formula.FormulaPoi;
@@ -116,6 +124,57 @@ class FormulaParserEvaluationTest {
assertTrue(result.getBooleanValue());
}
+ @Test
+ void dateFieldOnTheExcelEpochIsEvaluated() throws Exception {
+ // 1899-12-31 is the oldest representable date: it is Excel date serial
number 0.
+ CellValue result =
+ evaluate(List.of(field(new ValueMetaDate("start"), date(1899, 12,
31))), "[start]", false);
+
+ assertEquals(CellType.NUMERIC, result.getCellType());
+ assertEquals(0.0, result.getNumberValue());
+ }
+
+ @Test
+ void dateFieldBeforeTheExcelEpochIsRejected() {
+ HopValueException e =
+ assertThrows(
+ HopValueException.class,
+ () ->
+ evaluate(
+ List.of(field(new ValueMetaDate("start"), date(1800, 1,
1))),
+ "IF(1=2, DATE(2000,1,1), [start])",
+ false));
+
+ assertTrue(e.getMessage().contains("start"), e.getMessage());
+ assertTrue(e.getMessage().contains("1899-12-31"), e.getMessage());
+ }
+
+ @Test
+ void timestampFieldBeforeTheExcelEpochIsRejected() {
+ Timestamp timestamp = new Timestamp(date(1750, 6, 15).getTime());
+
+ HopValueException e =
+ assertThrows(
+ HopValueException.class,
+ () ->
+ evaluate(
+ List.of(field(new ValueMetaTimestamp("start"), timestamp)),
+ "[start] + 1",
+ false));
+
+ assertTrue(e.getMessage().contains("start"), e.getMessage());
+ }
+
+ @Test
+ void nullDateFieldIsNotRejected() throws Exception {
+ CellValue result =
+ evaluate(
+ List.of(field(new ValueMetaDate("start"), null)),
"IF(ISBLANK([start]), 1, 0)", false);
+
+ assertEquals(CellType.NUMERIC, result.getCellType());
+ assertEquals(1.0, result.getNumberValue());
+ }
+
@Test
void twoIntegerFieldsAreSummed() throws Exception {
CellValue result =
@@ -204,5 +263,11 @@ class FormulaParserEvaluationTest {
return new FieldBinding(meta, value);
}
+ /** POI converts dates in the default time zone, so build them in the
default zone as well. */
+ private static Date date(int year, int month, int day) {
+ return Date.from(
+ LocalDate.of(year, month,
day).atStartOfDay(ZoneId.systemDefault()).toInstant());
+ }
+
private record FieldBinding(IValueMeta meta, Object value) {}
}
diff --git a/tools/install-wave1-plugins.sh b/tools/install-wave1-plugins.sh
index 2e258232ec..80ba2e6e8e 100755
--- a/tools/install-wave1-plugins.sh
+++ b/tools/install-wave1-plugins.sh
@@ -25,7 +25,7 @@ set -euo pipefail
ROOT="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
INSTALL_DIR="${1:-${ROOT}/assemblies/client/target/hop}"
-VERSION="${HOP_VERSION:-2.19.0-SNAPSHOT}"
+VERSION="${HOP_VERSION:-2.20.0-SNAPSHOT}"
LIST_SCRIPT="${ROOT}/tools/list-marketplace-plugins.sh"
ZIP_DIR="${HOP_PLUGIN_ZIP_DIR:-}"
