You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hop.apache.org by ha...@apache.org on 2026/08/12 14:37:59 UTC
(hop) branch main updated: Formula transform hardening, fixes #3572 (#7916)
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 <ha...@gmail.com>
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:-}"