This is an automated email from the ASF dual-hosted git repository.
mattcasters 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 bf59265820 issue #7427 : Dimension Lookup/Update regression and bug
fix (#7429)
bf59265820 is described below
commit bf592658200ccc7e897d8f91894d88d3f702991a
Author: Matt Casters <[email protected]>
AuthorDate: Sat Jul 4 23:57:18 2026 +0200
issue #7427 : Dimension Lookup/Update regression and bug fix (#7429)
---
.../0012-6-dimension-lookup-no-version-field.hwf | 103 ++++++++++
.../database/0012-dimension-lookup-no-version.hpl | 168 +++++++++++++++++
.../0012-dimension-lookup-zero-length-validity.hpl | 138 ++++++++++++++
.../0012-dimension-lookup-zero-length-validity.hwf | 152 +++++++++++++++
.../golden-dimension-lookup-no-version.csv | 3 +
.../database/main-0012-dimension-lookup-update.hwf | 66 +++++++
.../golden-dimension-lookup-no-version.json | 32 ++++
.../0012-dimension-lookup-no-version UNIT.json | 38 ++++
.../scripts/script-dimension-lookup-no-version.sql | 38 ++++
.../script-dimension-zero-length-validity.sql | 38 ++++
.../transforms/dimensionlookup/DimensionCache.java | 208 +++++++++++++++++++++
.../dimensionlookup/DimensionLookup.java | 36 +++-
.../dimensionlookup/DimensionLookupDialog.java | 33 +++-
.../dimensionlookup/DimensionLookupMeta.java | 20 +-
.../messages/messages_en_US.properties | 10 +-
.../dimensionlookup/DimensionCacheTest.java | 158 ++++++++++++++++
16 files changed, 1228 insertions(+), 13 deletions(-)
diff --git
a/integration-tests/database/0012-6-dimension-lookup-no-version-field.hwf
b/integration-tests/database/0012-6-dimension-lookup-no-version-field.hwf
new file mode 100644
index 0000000000..330d5932e8
--- /dev/null
+++ b/integration-tests/database/0012-6-dimension-lookup-no-version-field.hwf
@@ -0,0 +1,103 @@
+<?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>0012-6-dimension-lookup-no-version-field</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <workflow_version/>
+ <created_user>-</created_user>
+ <created_date>2026/07/04 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/07/04 12:00:00.000</modified_date>
+ <parameters>
+ </parameters>
+ <actions>
+ <action>
+ <name>Start</name>
+ <description/>
+ <type>SPECIAL</type>
+ <attributes/>
+ <DayOfMonth>1</DayOfMonth>
+ <doNotWaitOnFirstExecution>N</doNotWaitOnFirstExecution>
+ <hour>12</hour>
+ <intervalMinutes>60</intervalMinutes>
+ <intervalSeconds>0</intervalSeconds>
+ <minutes>0</minutes>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <weekDay>1</weekDay>
+ <parallel>N</parallel>
+ <xloc>64</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>SQL</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <sql/>
+ <useVariableSubstitution>F</useVariableSubstitution>
+ <sqlfromfile>T</sqlfromfile>
+
<sqlfilename>${PROJECT_HOME}/scripts/script-dimension-lookup-no-version.sql</sqlfilename>
+ <sendOneStatement>F</sendOneStatement>
+ <connection>unit-test-db</connection>
+ <parallel>N</parallel>
+ <xloc>256</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Run Pipeline Unit Tests</name>
+ <description/>
+ <type>RunPipelineTests</type>
+ <attributes/>
+ <test_names>
+ <test_name>
+ <name>0012-dimension-lookup-no-version UNIT</name>
+ </test_name>
+ </test_names>
+ <parallel>N</parallel>
+ <xloc>480</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>Start</from>
+ <to>SQL</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>SQL</from>
+ <to>Run Pipeline Unit Tests</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ </hops>
+ <notepads>
+ </notepads>
+ <attributes/>
+</workflow>
\ No newline at end of file
diff --git a/integration-tests/database/0012-dimension-lookup-no-version.hpl
b/integration-tests/database/0012-dimension-lookup-no-version.hpl
new file mode 100644
index 0000000000..225a6b97fc
--- /dev/null
+++ b/integration-tests/database/0012-dimension-lookup-no-version.hpl
@@ -0,0 +1,168 @@
+<?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>0012-dimension-lookup-no-version</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <pipeline_status>0</pipeline_status>
+ <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/07/04 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/07/04 12:00:00.000</modified_date>
+ <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key>
+ <is_key_private>N</is_key_private>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>Data grid</from>
+ <to>Dimension lookup/update</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Dimension lookup/update</from>
+ <to>Verify</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Data grid</name>
+ <type>DataGrid</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <fields>
+ <field>
+ <currency/>
+ <decimal/>
+ <set_empty_string>N</set_empty_string>
+ <format/>
+ <group/>
+ <length>-1</length>
+ <name>key</name>
+ <precision>-1</precision>
+ <type>String</type>
+ </field>
+ </fields>
+ <data>
+ <line>
+ <item>1</item>
+ </line>
+ <line>
+ <item>2</item>
+ </line>
+ </data>
+ <attributes/>
+ <GUI>
+ <xloc>144</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Dimension lookup/update</name>
+ <type>DimensionLookup</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <schema>public</schema>
+ <table>dimension</table>
+ <connection>unit-test-db</connection>
+ <commit>100</commit>
+ <update>N</update>
+ <fields>
+ <key>
+ <name>key</name>
+ <lookup>key</lookup>
+ </key>
+ <date>
+ <name/>
+ <from>date_from</from>
+ <to>date_to</to>
+ </date>
+ <field>
+ <name>value</name>
+ <lookup>value</lookup>
+ <type>String</type>
+ </field>
+ <return>
+ <name>dimension_id</name>
+ <rename/>
+ <creation_method>tablemax</creation_method>
+ <use_autoinc>N</use_autoinc>
+ <version/>
+ </return>
+ </fields>
+ <sequence/>
+ <min_year>1900</min_year>
+ <max_year>2199</max_year>
+ <cache_size>0</cache_size>
+ <preload_cache>Y</preload_cache>
+ <ignore_zero_length_validity>N</ignore_zero_length_validity>
+ <use_start_date_alternative>N</use_start_date_alternative>
+ <start_date_alternative>none</start_date_alternative>
+ <start_date_field_name/>
+ <useBatch>N</useBatch>
+ <attributes/>
+ <GUI>
+ <xloc>364</xloc>
+ <yloc>94</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Verify</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>576</xloc>
+ <yloc>94</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
\ No newline at end of file
diff --git
a/integration-tests/database/0012-dimension-lookup-zero-length-validity.hpl
b/integration-tests/database/0012-dimension-lookup-zero-length-validity.hpl
new file mode 100644
index 0000000000..9f624d81d0
--- /dev/null
+++ b/integration-tests/database/0012-dimension-lookup-zero-length-validity.hpl
@@ -0,0 +1,138 @@
+<?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>0012-dimension-lookup-zero-length-validity</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <pipeline_status>0</pipeline_status>
+ <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/07/04 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/07/04 12:00:00.000</modified_date>
+ <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key>
+ <is_key_private>N</is_key_private>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>Data grid</from>
+ <to>Dimension lookup/update</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Data grid</name>
+ <type>DataGrid</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <fields>
+ <field>
+ <currency/>
+ <decimal/>
+ <set_empty_string>N</set_empty_string>
+ <format/>
+ <group/>
+ <length>-1</length>
+ <name>key</name>
+ <precision>-1</precision>
+ <type>String</type>
+ </field>
+ </fields>
+ <data>
+ <line>
+ <item>K1</item>
+ </line>
+ </data>
+ <attributes/>
+ <GUI>
+ <xloc>144</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>Dimension lookup/update</name>
+ <type>DimensionLookup</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <schema>public</schema>
+ <table>dimension</table>
+ <connection>unit-test-db</connection>
+ <commit>100</commit>
+ <update>N</update>
+ <fields>
+ <key>
+ <name>key</name>
+ <lookup>key</lookup>
+ </key>
+ <date>
+ <name/>
+ <from>date_from</from>
+ <to>date_to</to>
+ </date>
+ <return>
+ <name>dimension_id</name>
+ <rename/>
+ <creation_method>tablemax</creation_method>
+ <use_autoinc>N</use_autoinc>
+ <version/>
+ </return>
+ </fields>
+ <sequence/>
+ <min_year>1900</min_year>
+ <max_year>2199</max_year>
+ <cache_size>0</cache_size>
+ <preload_cache>Y</preload_cache>
+ <ignore_zero_length_validity>N</ignore_zero_length_validity>
+ <use_start_date_alternative>N</use_start_date_alternative>
+ <start_date_alternative>none</start_date_alternative>
+ <start_date_field_name/>
+ <useBatch>N</useBatch>
+ <attributes/>
+ <GUI>
+ <xloc>364</xloc>
+ <yloc>94</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
\ No newline at end of file
diff --git
a/integration-tests/database/0012-dimension-lookup-zero-length-validity.hwf
b/integration-tests/database/0012-dimension-lookup-zero-length-validity.hwf
new file mode 100644
index 0000000000..e59fe4d6b6
--- /dev/null
+++ b/integration-tests/database/0012-dimension-lookup-zero-length-validity.hwf
@@ -0,0 +1,152 @@
+<?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>0012-dimension-lookup-zero-length-validity</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <workflow_version/>
+ <created_user>-</created_user>
+ <created_date>2026/07/04 12:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/07/04 12:00:00.000</modified_date>
+ <parameters>
+ </parameters>
+ <actions>
+ <action>
+ <name>Start</name>
+ <description/>
+ <type>SPECIAL</type>
+ <attributes/>
+ <DayOfMonth>1</DayOfMonth>
+ <doNotWaitOnFirstExecution>N</doNotWaitOnFirstExecution>
+ <hour>12</hour>
+ <intervalMinutes>60</intervalMinutes>
+ <intervalSeconds>0</intervalSeconds>
+ <minutes>0</minutes>
+ <repeat>N</repeat>
+ <schedulerType>0</schedulerType>
+ <weekDay>1</weekDay>
+ <parallel>N</parallel>
+ <xloc>64</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>SQL</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <sql/>
+ <useVariableSubstitution>F</useVariableSubstitution>
+ <sqlfromfile>T</sqlfromfile>
+
<sqlfilename>${PROJECT_HOME}/scripts/script-dimension-zero-length-validity.sql</sqlfilename>
+ <sendOneStatement>F</sendOneStatement>
+ <connection>unit-test-db</connection>
+ <parallel>N</parallel>
+ <xloc>208</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0012-dimension-lookup-zero-length-validity.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+
<filename>${PROJECT_HOME}/0012-dimension-lookup-zero-length-validity.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>
+ <set_logfile>N</set_logfile>
+ <logfile/>
+ <logext/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <loglevel>Basic</loglevel>
+ <set_append_logfile>N</set_append_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <create_parent_folder>N</create_parent_folder>
+ <run_configuration>local</run_configuration>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <parallel>N</parallel>
+ <xloc>448</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Success</name>
+ <description/>
+ <type>SUCCESS</type>
+ <attributes/>
+ <parallel>N</parallel>
+ <xloc>688</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>Abort workflow</name>
+ <description/>
+ <type>ABORT</type>
+ <attributes/>
+ <always_log_rows>N</always_log_rows>
+ <message>Failure: preload cache should have failed on zero-length
validity data</message>
+ <parallel>N</parallel>
+ <xloc>448</xloc>
+ <yloc>160</yloc>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>Start</from>
+ <to>SQL</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>SQL</from>
+ <to>0012-dimension-lookup-zero-length-validity.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0012-dimension-lookup-zero-length-validity.hpl</from>
+ <to>Success</to>
+ <enabled>Y</enabled>
+ <evaluation>N</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0012-dimension-lookup-zero-length-validity.hpl</from>
+ <to>Abort workflow</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ </hops>
+ <notepads>
+ </notepads>
+ <attributes/>
+</workflow>
\ No newline at end of file
diff --git
a/integration-tests/database/datasets/golden-dimension-lookup-no-version.csv
b/integration-tests/database/datasets/golden-dimension-lookup-no-version.csv
new file mode 100644
index 0000000000..d9d1a32a52
--- /dev/null
+++ b/integration-tests/database/datasets/golden-dimension-lookup-no-version.csv
@@ -0,0 +1,3 @@
+key,dimension_id,value
+1,1,alpha
+2,2,beta
\ No newline at end of file
diff --git a/integration-tests/database/main-0012-dimension-lookup-update.hwf
b/integration-tests/database/main-0012-dimension-lookup-update.hwf
index bb669cabc7..6c7f990903 100644
--- a/integration-tests/database/main-0012-dimension-lookup-update.hwf
+++ b/integration-tests/database/main-0012-dimension-lookup-update.hwf
@@ -173,6 +173,58 @@ limitations under the License.
<yloc>368</yloc>
<attributes_hac/>
</action>
+ <action>
+ <name>0012-6-dimension-lookup-no-version-field.hwf</name>
+ <description/>
+ <type>WORKFLOW</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+
<filename>${PROJECT_HOME}/0012-6-dimension-lookup-no-version-field.hwf</filename>
+ <logext/>
+ <logfile/>
+ <loglevel>Nothing</loglevel>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <parallel>N</parallel>
+ <xloc>320</xloc>
+ <yloc>448</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0012-dimension-lookup-zero-length-validity.hwf</name>
+ <description/>
+ <type>WORKFLOW</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+
<filename>${PROJECT_HOME}/0012-dimension-lookup-zero-length-validity.hwf</filename>
+ <logext/>
+ <logfile/>
+ <loglevel>Nothing</loglevel>
+ <parameters>
+ <pass_all_parameters>Y</pass_all_parameters>
+ </parameters>
+ <params_from_previous>N</params_from_previous>
+ <run_configuration>local</run_configuration>
+ <set_append_logfile>N</set_append_logfile>
+ <set_logfile>N</set_logfile>
+ <wait_until_finished>Y</wait_until_finished>
+ <parallel>N</parallel>
+ <xloc>320</xloc>
+ <yloc>528</yloc>
+ <attributes_hac/>
+ </action>
</actions>
<hops>
<hop>
@@ -210,6 +262,20 @@ limitations under the License.
<evaluation>Y</evaluation>
<unconditional>N</unconditional>
</hop>
+ <hop>
+ <from>0012-5-dimension-lookup-tk-uuid.hwf</from>
+ <to>0012-6-dimension-lookup-no-version-field.hwf</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0012-6-dimension-lookup-no-version-field.hwf</from>
+ <to>0012-dimension-lookup-zero-length-validity.hwf</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
</hops>
<notepads>
</notepads>
diff --git
a/integration-tests/database/metadata/dataset/golden-dimension-lookup-no-version.json
b/integration-tests/database/metadata/dataset/golden-dimension-lookup-no-version.json
new file mode 100644
index 0000000000..da990ec9c2
--- /dev/null
+++
b/integration-tests/database/metadata/dataset/golden-dimension-lookup-no-version.json
@@ -0,0 +1,32 @@
+{
+ "base_filename": "golden-dimension-lookup-no-version.csv",
+ "name": "golden-dimension-lookup-no-version",
+ "description": "",
+ "dataset_fields": [
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "key",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": 15,
+ "field_type": 5,
+ "field_precision": 0,
+ "field_name": "dimension_id",
+ "field_format": "####0;-####0"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "value",
+ "field_format": ""
+ }
+ ],
+ "folder_name": "${HOP_DATASETS_FOLDER}"
+}
\ No newline at end of file
diff --git
a/integration-tests/database/metadata/unit-test/0012-dimension-lookup-no-version
UNIT.json
b/integration-tests/database/metadata/unit-test/0012-dimension-lookup-no-version
UNIT.json
new file mode 100644
index 0000000000..85d7d2ad43
--- /dev/null
+++
b/integration-tests/database/metadata/unit-test/0012-dimension-lookup-no-version
UNIT.json
@@ -0,0 +1,38 @@
+{
+ "variableValues": [],
+ "database_replacements": [],
+ "autoOpening": true,
+ "basePath": "",
+ "golden_data_sets": [
+ {
+ "field_mappings": [
+ {
+ "transform_field": "key",
+ "data_set_field": "key"
+ },
+ {
+ "transform_field": "dimension_id",
+ "data_set_field": "dimension_id"
+ },
+ {
+ "transform_field": "value",
+ "data_set_field": "value"
+ }
+ ],
+ "field_order": [
+ "key",
+ "dimension_id",
+ "value"
+ ],
+ "data_set_name": "golden-dimension-lookup-no-version",
+ "transform_name": "Verify"
+ }
+ ],
+ "input_data_sets": [],
+ "name": "0012-dimension-lookup-no-version UNIT",
+ "description": "",
+ "persist_filename": "",
+ "trans_test_tweaks": [],
+ "pipeline_filename": "./0012-dimension-lookup-no-version.hpl",
+ "test_type": "UNIT_TEST"
+}
\ No newline at end of file
diff --git
a/integration-tests/database/scripts/script-dimension-lookup-no-version.sql
b/integration-tests/database/scripts/script-dimension-lookup-no-version.sql
new file mode 100644
index 0000000000..afe60aa6a6
--- /dev/null
+++ b/integration-tests/database/scripts/script-dimension-lookup-no-version.sql
@@ -0,0 +1,38 @@
+/*
+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.
+*/
+
+DROP TABLE IF EXISTS public.dimension;
+
+CREATE TABLE public.dimension
+(
+ dimension_id BIGSERIAL,
+ version INTEGER,
+ date_from TIMESTAMP,
+ date_to TIMESTAMP,
+ "key" TEXT,
+ "value" TEXT
+)
+;
+CREATE INDEX idx_dimension_lookup ON "public".dimension ("key")
+;
+CREATE INDEX idx_dimension_tk ON "public".dimension (dimension_id)
+;
+
+INSERT INTO public.dimension (version, date_from, date_to, "key", "value")
+VALUES (1, '1900-01-01', '2199-01-01', '1', 'alpha'),
+ (1, '1900-01-01', '2199-01-01', '2', 'beta')
+;
\ No newline at end of file
diff --git
a/integration-tests/database/scripts/script-dimension-zero-length-validity.sql
b/integration-tests/database/scripts/script-dimension-zero-length-validity.sql
new file mode 100644
index 0000000000..50e74c57b9
--- /dev/null
+++
b/integration-tests/database/scripts/script-dimension-zero-length-validity.sql
@@ -0,0 +1,38 @@
+/*
+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.
+*/
+
+DROP TABLE IF EXISTS public.dimension;
+
+CREATE TABLE public.dimension
+(
+ dimension_id BIGSERIAL,
+ version INTEGER,
+ date_from TIMESTAMP,
+ date_to TIMESTAMP,
+ "key" TEXT,
+ "value" TEXT
+)
+;
+CREATE INDEX idx_dimension_lookup ON "public".dimension ("key")
+;
+CREATE INDEX idx_dimension_tk ON "public".dimension (dimension_id)
+;
+
+INSERT INTO public.dimension (version, date_from, date_to, "key", "value")
+VALUES (1, '2020-01-01', '2020-06-01', 'K1', 'wide-range'),
+ (1, '2020-03-01', '2020-03-01', 'K1', 'zero-length')
+;
\ No newline at end of file
diff --git
a/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionCache.java
b/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionCache.java
index 3c929e2d92..fd0b980ff3 100644
---
a/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionCache.java
+++
b/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionCache.java
@@ -17,14 +17,18 @@
package org.apache.hop.pipeline.transforms.dimensionlookup;
+import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
+import org.apache.hop.core.Const;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.exception.HopRuntimeException;
+import org.apache.hop.core.exception.HopTransformException;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.i18n.BaseMessages;
/**
* This class will act as a special purpose dimension Cache. The idea here is
to not only cache the
@@ -35,6 +39,9 @@ import org.apache.hop.core.row.IValueMeta;
* conversion errors as possible, we'll use the same row as we get from the
database.
*/
public class DimensionCache implements Comparator<Object[]> {
+ private static final Class<?> PKG = DimensionLookupMeta.class;
+ private static final int MAX_VALIDATION_ERRORS = 3;
+
private IRowMeta rowMeta;
private List<Object[]> rowCache;
private int[] keyIndexes;
@@ -56,6 +63,43 @@ public class DimensionCache implements Comparator<Object[]> {
this.toDateIndex = toDateIndex;
}
+ /**
+ * Returns true when both dates are non-null and equal, representing a
zero-length [from, to)
+ * validity interval.
+ */
+ static boolean isZeroLengthValidity(Date fromDate, Date toDate) {
+ return fromDate != null
+ && toDate != null
+ && !fromDate.before(toDate)
+ && !toDate.before(fromDate);
+ }
+
+ /**
+ * Returns a new list that excludes rows with zero-length validity intervals.
+ *
+ * @param rowMeta the row layout of the cache rows
+ * @param rows the rows loaded from the dimension table
+ * @param fromDateIndex the index of the date-from field
+ * @param toDateIndex the index of the date-to field
+ * @return the filtered rows
+ */
+ public static List<Object[]> excludeZeroLengthValidityRows(
+ IRowMeta rowMeta, List<Object[]> rows, int fromDateIndex, int
toDateIndex) {
+ List<Object[]> filteredRows = new ArrayList<>(rows.size());
+ for (Object[] row : rows) {
+ try {
+ Date fromDate =
rowMeta.getValueMeta(fromDateIndex).getDate(row[fromDateIndex]);
+ Date toDate =
rowMeta.getValueMeta(toDateIndex).getDate(row[toDateIndex]);
+ if (!isZeroLengthValidity(fromDate, toDate)) {
+ filteredRows.add(row);
+ }
+ } catch (Exception e) {
+ throw new HopRuntimeException(e);
+ }
+ }
+ return filteredRows;
+ }
+
/**
* Add a row to the back of the list
*
@@ -174,10 +218,174 @@ public class DimensionCache implements
Comparator<Object[]> {
}
}
+ /**
+ * Validate dimension rows before sorting the cache. Overlapping or invalid
date ranges for the
+ * same natural key cause the lookup comparator to violate its contract
during {@link
+ * #sortRows()}.
+ *
+ * @param schemaTable the quoted schema/table combination for error reporting
+ * @throws HopTransformException when the cache data cannot be sorted safely
+ */
+ public void validateRowsForSort(String schemaTable) throws
HopTransformException {
+ try {
+ if (rowCache == null || rowCache.isEmpty()) {
+ return;
+ }
+
+ List<String> errors = new ArrayList<>();
+
+ for (Object[] row : rowCache) {
+ if (errors.size() >= MAX_VALIDATION_ERRORS) {
+ break;
+ }
+ Date fromDate = getDateFromRow(row, fromDateIndex);
+ Date toDate = getDateFromRow(row, toDateIndex);
+ if (isZeroLengthValidity(fromDate, toDate)
+ || (fromDate != null && toDate != null && fromDate.after(toDate)))
{
+ errors.add(
+ BaseMessages.getString(
+ PKG,
+ "DimensionLookup.Exception.CacheInvalidDateRange",
+ formatNaturalKey(row),
+ formatDateRange(fromDate, toDate)));
+ }
+ }
+
+ List<Object[]> sorted = new ArrayList<>(rowCache);
+ sorted.sort(
+ (o1, o2) -> {
+ int cmp = compareNaturalKeys(o1, o2);
+ if (cmp != 0) {
+ return cmp;
+ }
+ return compareFromDates(o1, o2);
+ });
+
+ for (int i = 1; i < sorted.size() && errors.size() <
MAX_VALIDATION_ERRORS; i++) {
+ Object[] previousRow = sorted.get(i - 1);
+ Object[] currentRow = sorted.get(i);
+ if (compareNaturalKeys(previousRow, currentRow) != 0) {
+ continue;
+ }
+
+ Date previousFrom = getDateFromRow(previousRow, fromDateIndex);
+ Date previousTo = getDateFromRow(previousRow, toDateIndex);
+ Date currentFrom = getDateFromRow(currentRow, fromDateIndex);
+ Date currentTo = getDateFromRow(currentRow, toDateIndex);
+ if (rangesOverlap(previousFrom, previousTo, currentFrom, currentTo)) {
+ errors.add(
+ BaseMessages.getString(
+ PKG,
+ "DimensionLookup.Exception.CacheOverlappingDateRanges",
+ formatNaturalKey(previousRow),
+ formatDateRange(previousFrom, previousTo),
+ formatDateRange(currentFrom, currentTo)));
+ }
+ }
+
+ if (!errors.isEmpty()) {
+ StringBuilder message = new StringBuilder();
+ message.append(
+ BaseMessages.getString(
+ PKG, "DimensionLookup.Exception.CacheSortValidationFailed",
schemaTable));
+ for (String error : errors) {
+ message.append(Const.CR).append(error);
+ }
+ if (errors.size() >= MAX_VALIDATION_ERRORS) {
+ message.append(Const.CR).append("...");
+ }
+ throw new HopTransformException(message.toString());
+ }
+ } catch (HopTransformException e) {
+ throw e;
+ } catch (HopRuntimeException e) {
+ throw new HopTransformException(
+ BaseMessages.getString(
+ PKG, "DimensionLookup.Exception.CacheSortValidationFailed",
schemaTable),
+ e);
+ }
+ }
+
public void sortRows() {
Collections.sort(rowCache, this);
}
+ private int compareNaturalKeys(Object[] row1, Object[] row2) {
+ try {
+ return rowMeta.compare(row1, row2, keyIndexes);
+ } catch (Exception e) {
+ throw new HopRuntimeException(e);
+ }
+ }
+
+ private Date getDateFromRow(Object[] row, int dateIndex) {
+ try {
+ return rowMeta.getValueMeta(dateIndex).getDate(row[dateIndex]);
+ } catch (Exception e) {
+ throw new HopRuntimeException(e);
+ }
+ }
+
+ private int compareFromDates(Object[] row1, Object[] row2) {
+ Date from1 = getDateFromRow(row1, fromDateIndex);
+ Date from2 = getDateFromRow(row2, fromDateIndex);
+ if (from1 == null && from2 == null) {
+ return 0;
+ }
+ if (from1 == null) {
+ return -1;
+ }
+ if (from2 == null) {
+ return 1;
+ }
+ return from1.compareTo(from2);
+ }
+
+ /**
+ * Returns true when the half-open intervals [from1, to1) and [from2, to2)
overlap. A null from
+ * date means -infinity and a null to date means +infinity.
+ */
+ private boolean rangesOverlap(Date from1, Date to1, Date from2, Date to2) {
+ boolean from1BeforeTo2 = (to2 == null) || (from1 == null) ||
from1.before(to2);
+ boolean from2BeforeTo1 = (to1 == null) || (from2 == null) ||
from2.before(to1);
+ return from1BeforeTo2 && from2BeforeTo1;
+ }
+
+ private String formatNaturalKey(Object[] row) {
+ try {
+ if (keyIndexes == null || keyIndexes.length == 0) {
+ return "(no natural key)";
+ }
+
+ StringBuilder naturalKey = new StringBuilder();
+ for (int i = 0; i < keyIndexes.length; i++) {
+ if (i > 0) {
+ naturalKey.append(", ");
+ }
+ naturalKey.append(rowMeta.getString(row, keyIndexes[i]));
+ }
+ return naturalKey.toString();
+ } catch (Exception e) {
+ throw new HopRuntimeException(e);
+ }
+ }
+
+ private String formatDateRange(Date fromDate, Date toDate) {
+ try {
+ String fromDateText = "-infinity";
+ String toDateText = "+infinity";
+ if (fromDate != null) {
+ fromDateText = rowMeta.getValueMeta(fromDateIndex).getString(fromDate);
+ }
+ if (toDate != null) {
+ toDateText = rowMeta.getValueMeta(toDateIndex).getString(toDate);
+ }
+ return "[" + fromDateText + ", " + toDateText + ")";
+ } catch (Exception e) {
+ throw new HopRuntimeException(e);
+ }
+ }
+
/**
* Compare 2 rows of data using the natural keys and indexes specified.
*
diff --git
a/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookup.java
b/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookup.java
index 0284466f33..2f3b38e3b4 100644
---
a/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookup.java
+++
b/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookup.java
@@ -216,7 +216,8 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
} catch (HopException e) {
logError(
BaseMessages.getString(
- PKG, "DimensionLookup.Log.TransformCanNotContinueForErrors",
e.getMessage()));
+ PKG, "DimensionLookup.Log.TransformCanNotContinueForErrors",
e.getMessage()),
+ e);
setErrors(1);
stopAll();
setOutputDone();
@@ -415,10 +416,27 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
data.schemaTable));
}
+ if (meta.isIgnoreZeroLengthValidity()) {
+ int originalSize = rows.size();
+ rows =
+ DimensionCache.excludeZeroLengthValidityRows(
+ rowMeta, rows, data.preloadFromDateIndex,
data.preloadToDateIndex);
+ int skipped = originalSize - rows.size();
+ if (skipped > 0 && isDetailed()) {
+ logDetailed(
+ BaseMessages.getString(
+ PKG, "DimensionLookup.Log.SkippedZeroLengthValidityRows",
skipped));
+ }
+ if (rows.isEmpty()) {
+ logBasic(BaseMessages.getString(PKG,
"DimensionLookup.Log.EmptyPreloadCacheAfterFilter"));
+ }
+ }
+
data.preloadCache =
new DimensionCache(
rowMeta, data.preloadKeyIndexes, data.preloadFromDateIndex,
data.preloadToDateIndex);
data.preloadCache.setRowCache(rows);
+ data.preloadCache.validateRowsForSort(data.schemaTable);
if (isDetailed()) {
logDetailed("Sorting the cache rows...");
@@ -446,6 +464,8 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
}
// This is all for now...
+ } catch (HopTransformException e) {
+ throw e;
} catch (Exception e) {
throw new HopException("Error encountered during cache pre-load", e);
}
@@ -503,9 +523,13 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
lookupRow[to] = toData;
}
- // Also set the lookup date on the "end of date range" (toDate) position
+ // Also set the lookup date for the date-range comparison in the cache
//
- lookupRow[data.preloadFromDateIndex] = valueDate;
+ IValueMeta preloadFromDateMeta =
+
data.preloadCache.getRowMeta().getValueMeta(data.preloadFromDateIndex);
+ IValueMeta valueDateMeta = new ValueMetaDate("lookupDate");
+ lookupRow[data.preloadFromDateIndex] =
+ preloadFromDateMeta.convertData(valueDateMeta, valueDate);
// Look up the row in the pre-load cache...
//
@@ -1981,8 +2005,10 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
data.schemaTable));
}
} else {
- throw new HopTransformException(
- BaseMessages.getString(PKG,
"DimensionLookupMeta.CheckResult.VersionKeyRequired"));
+ if (meta.isUpdate()) {
+ throw new HopTransformException(
+ BaseMessages.getString(PKG,
"DimensionLookupMeta.CheckResult.VersionKeyRequired"));
+ }
}
String fromField = f.getDate().getFrom();
diff --git
a/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupDialog.java
b/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupDialog.java
index 5f9da8e43e..8292e6cfd3 100644
---
a/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupDialog.java
+++
b/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupDialog.java
@@ -104,6 +104,9 @@ public class DimensionLookupDialog extends
BaseTransformDialog {
private Label wlPreloadCache;
private Button wPreloadCache;
+ private Label wlIgnoreZeroLengthValidity;
+ private Button wIgnoreZeroLengthValidity;
+
private Label wlCacheSize;
private Text wCacheSize;
@@ -399,6 +402,26 @@ public class DimensionLookupDialog extends
BaseTransformDialog {
fdPreloadCache.right = new FormAttachment(100, 0);
wPreloadCache.setLayoutData(fdPreloadCache);
+ wlIgnoreZeroLengthValidity = new Label(wPhysicalComp, SWT.RIGHT);
+ wlIgnoreZeroLengthValidity.setText(
+ BaseMessages.getString(PKG,
"DimensionLookupDialog.IgnoreZeroLengthValidity.Label"));
+ PropsUi.setLook(wlIgnoreZeroLengthValidity);
+ FormData fdlIgnoreZeroLengthValidity = new FormData();
+ fdlIgnoreZeroLengthValidity.left = new FormAttachment(0, 0);
+ fdlIgnoreZeroLengthValidity.right = new FormAttachment(middle, -margin);
+ fdlIgnoreZeroLengthValidity.top = new FormAttachment(wPreloadCache,
margin);
+ wlIgnoreZeroLengthValidity.setLayoutData(fdlIgnoreZeroLengthValidity);
+ wIgnoreZeroLengthValidity = new Button(wPhysicalComp, SWT.CHECK);
+ PropsUi.setLook(wIgnoreZeroLengthValidity);
+ wIgnoreZeroLengthValidity.setToolTipText(
+ BaseMessages.getString(PKG,
"DimensionLookupDialog.IgnoreZeroLengthValidity.Tooltip"));
+ wIgnoreZeroLengthValidity.addListener(SWT.Selection, e -> setFlags());
+ FormData fdIgnoreZeroLengthValidity = new FormData();
+ fdIgnoreZeroLengthValidity.left = new FormAttachment(middle, 0);
+ fdIgnoreZeroLengthValidity.top = new
FormAttachment(wlIgnoreZeroLengthValidity, 0, SWT.CENTER);
+ fdIgnoreZeroLengthValidity.right = new FormAttachment(100, 0);
+ wIgnoreZeroLengthValidity.setLayoutData(fdIgnoreZeroLengthValidity);
+
// Cache size ...
wlCacheSize = new Label(wPhysicalComp, SWT.RIGHT);
wlCacheSize.setText(BaseMessages.getString(PKG,
"DimensionLookupDialog.CacheSize.Label"));
@@ -406,13 +429,13 @@ public class DimensionLookupDialog extends
BaseTransformDialog {
FormData fdlCacheSize = new FormData();
fdlCacheSize.left = new FormAttachment(0, 0);
fdlCacheSize.right = new FormAttachment(middle, -margin);
- fdlCacheSize.top = new FormAttachment(wPreloadCache, margin);
+ fdlCacheSize.top = new FormAttachment(wIgnoreZeroLengthValidity, margin);
wlCacheSize.setLayoutData(fdlCacheSize);
wCacheSize = new Text(wPhysicalComp, SWT.SINGLE | SWT.LEFT | SWT.BORDER);
PropsUi.setLook(wCacheSize);
FormData fdCacheSize = new FormData();
fdCacheSize.left = new FormAttachment(middle, 0);
- fdCacheSize.top = new FormAttachment(wPreloadCache, margin);
+ fdCacheSize.top = new FormAttachment(wlCacheSize, 0, SWT.CENTER);
fdCacheSize.right = new FormAttachment(100, 0);
wCacheSize.setLayoutData(fdCacheSize);
@@ -1174,6 +1197,10 @@ public class DimensionLookupDialog extends
BaseTransformDialog {
wlPreloadCache.setEnabled(wUseCache.getSelection() &&
!wUpdate.getSelection());
wPreloadCache.setEnabled(wUseCache.getSelection() &&
!wUpdate.getSelection());
+ boolean preloadCacheEnabled = wUseCache.getSelection() &&
!wUpdate.getSelection();
+ wlIgnoreZeroLengthValidity.setEnabled(preloadCacheEnabled &&
wPreloadCache.getSelection());
+ wIgnoreZeroLengthValidity.setEnabled(preloadCacheEnabled &&
wPreloadCache.getSelection());
+
wlCacheSize.setEnabled(wUseCache.getSelection() &&
!wPreloadCache.getSelection());
wCacheSize.setEnabled(wUseCache.getSelection() &&
!wPreloadCache.getSelection());
@@ -1271,6 +1298,7 @@ public class DimensionLookupDialog extends
BaseTransformDialog {
wUseCache.setSelection(input.getCacheSize() >= 0);
wPreloadCache.setSelection(input.isPreloadingCache());
+ wIgnoreZeroLengthValidity.setSelection(input.isIgnoreZeroLengthValidity());
wCacheSize.setText("" + input.getCacheSize());
wMinYear.setText("" + input.getMinYear());
@@ -1394,6 +1422,7 @@ public class DimensionLookupDialog extends
BaseTransformDialog {
in.setCacheSize(-1);
}
in.setPreloadingCache(wPreloadCache.getSelection());
+ in.setIgnoreZeroLengthValidity(wIgnoreZeroLengthValidity.getSelection());
if (wPreloadCache.getSelection()) {
in.setCacheSize(0);
}
diff --git
a/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupMeta.java
b/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupMeta.java
index b394858ae3..e2432c9106 100644
---
a/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupMeta.java
+++
b/plugins/transforms/dimensionlookup/src/main/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupMeta.java
@@ -182,6 +182,12 @@ public class DimensionLookupMeta extends
BaseTransformMeta<DimensionLookup, Dime
injectionKeyDescription = "DimensionLookup.Injection.PRELOAD_CACHE")
private boolean preloadingCache;
+ @HopMetadataProperty(
+ key = "ignore_zero_length_validity",
+ injectionKey = "IGNORE_ZERO_LENGTH_VALIDITY",
+ injectionKeyDescription =
"DimensionLookup.Injection.IGNORE_ZERO_LENGTH_VALIDITY")
+ private boolean ignoreZeroLengthValidity;
+
@HopMetadataProperty(
key = "unknown_row_check_disabled",
injectionKey = "UNKNOWN_ROW_CHECK_DISABLED",
@@ -223,6 +229,7 @@ public class DimensionLookupMeta extends
BaseTransformMeta<DimensionLookup, Dime
this.startDateAlternative = m.startDateAlternative;
this.startDateFieldName = m.startDateFieldName;
this.preloadingCache = m.preloadingCache;
+ this.ignoreZeroLengthValidity = m.ignoreZeroLengthValidity;
this.unknownRowCheckDisabled = m.unknownRowCheckDisabled;
}
@@ -249,6 +256,7 @@ public class DimensionLookupMeta extends
BaseTransformMeta<DimensionLookup, Dime
cacheSize = 5000;
preloadingCache = false;
+ ignoreZeroLengthValidity = false;
unknownRowCheckDisabled = false;
}
@@ -667,11 +675,13 @@ public class DimensionLookupMeta extends
BaseTransformMeta<DimensionLookup, Dime
transformMeta));
}
} else {
- remarks.add(
- new CheckResult(
- ICheckResult.TYPE_RESULT_ERROR,
- BaseMessages.getString(PKG,
"DimensionLookupMeta.CheckResult.VersionKeyRequired"),
- transformMeta));
+ if (isUpdate()) {
+ remarks.add(
+ new CheckResult(
+ ICheckResult.TYPE_RESULT_ERROR,
+ BaseMessages.getString(PKG,
"DimensionLookupMeta.CheckResult.VersionKeyRequired"),
+ transformMeta));
+ }
}
TechnicalKeyCreationMethod method = fields.returns.creationMethod;
diff --git
a/plugins/transforms/dimensionlookup/src/main/resources/org/apache/hop/pipeline/transforms/dimensionlookup/messages/messages_en_US.properties
b/plugins/transforms/dimensionlookup/src/main/resources/org/apache/hop/pipeline/transforms/dimensionlookup/messages/messages_en_US.properties
index 5a08374572..2939c00228 100644
---
a/plugins/transforms/dimensionlookup/src/main/resources/org/apache/hop/pipeline/transforms/dimensionlookup/messages/messages_en_US.properties
+++
b/plugins/transforms/dimensionlookup/src/main/resources/org/apache/hop/pipeline/transforms/dimensionlookup/messages/messages_en_US.properties
@@ -236,4 +236,12 @@ DimensionLookup.Exception.LookupKeyFieldNotFound=Lookup
key column [{0}] not fou
DimensionLookup.Exception.LookupOrUpdateFieldNotFound=Lookup/update column
[{0}] not found in table [{1}].
DimensionLookup.Exception.DateFieldNameNotFound=Date field [{0}] could not be
found in the input stream.
DimensionLookup.Exception.TkSourceFieldNotFound=Technical key source field
[{0}] could not be found in the input stream.
-DimensionLookup.Exception.UpdateSourceFieldNotFound=Update source field [{0}]
could not be found in the input stream.
\ No newline at end of file
+DimensionLookup.Exception.UpdateSourceFieldNotFound=Update source field [{0}]
could not be found in the input stream.
+DimensionLookup.Exception.CacheSortValidationFailed=Cannot sort pre-loaded
dimension cache for table [{0}]. The dimension data contains date range
problems:
+DimensionLookup.Exception.CacheInvalidDateRange=Natural key [{0}] has an
invalid date range {1} (date-from must be before date-to).
+DimensionLookup.Exception.CacheOverlappingDateRanges=Natural key [{0}] has
overlapping validity periods {1} and {2}.
+DimensionLookupDialog.IgnoreZeroLengthValidity.Label=Ignore zero-length
validity records
+DimensionLookupDialog.IgnoreZeroLengthValidity.Tooltip=Skip dimension rows
where date-from equals date-to when pre-loading the cache. Useful when upstream
flattening produces point-in-time records that cannot be looked up.
+DimensionLookup.Injection.IGNORE_ZERO_LENGTH_VALIDITY=Skip rows where
date-from equals date-to when pre-loading the cache.
+DimensionLookup.Log.SkippedZeroLengthValidityRows=Skipped {0} zero-length
validity row(s) from preload cache.
+DimensionLookup.Log.EmptyPreloadCacheAfterFilter=Preload cache is empty after
ignoring zero-length validity records.
\ No newline at end of file
diff --git
a/plugins/transforms/dimensionlookup/src/test/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionCacheTest.java
b/plugins/transforms/dimensionlookup/src/test/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionCacheTest.java
index c34a1c682f..f68aab9769 100644
---
a/plugins/transforms/dimensionlookup/src/test/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionCacheTest.java
+++
b/plugins/transforms/dimensionlookup/src/test/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionCacheTest.java
@@ -17,10 +17,20 @@
package org.apache.hop.pipeline.transforms.dimensionlookup;
+import static org.junit.jupiter.api.Assertions.assertDoesNotThrow;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertThrows;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
import java.sql.Timestamp;
+import java.util.ArrayList;
import java.util.Date;
+import java.util.List;
+import org.apache.hop.core.exception.HopTransformException;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaString;
import org.apache.hop.core.row.value.ValueMetaTimestamp;
import org.apache.hop.core.util.Assert;
import org.junit.jupiter.api.Test;
@@ -68,6 +78,154 @@ class DimensionCacheTest {
assertCompareDateInterval(dc, null, D4, D5, null, -1); // NPE
}
+ @Test
+ void testValidateRowsForSortAcceptsValidAdjacentRanges() {
+ DimensionCache cache = createKeyedCache();
+ Date d1 = timestamp(0);
+ Date d2 = timestamp(1);
+ Date d3 = timestamp(2);
+
+ List<Object[]> rows = new ArrayList<>();
+ rows.add(new Object[] {"K1", d1, d2});
+ rows.add(new Object[] {"K1", d2, d3});
+ cache.setRowCache(rows);
+
+ assertDoesNotThrow(() -> cache.validateRowsForSort("dim_customer"));
+ assertDoesNotThrow(cache::sortRows);
+ }
+
+ @Test
+ void testValidateRowsForSortRejectsOverlappingRanges() {
+ DimensionCache cache = createKeyedCache();
+ Date d1 = timestamp(0);
+ Date d2 = timestamp(1);
+ Date d3 = timestamp(2);
+ Date d4 = timestamp(3);
+
+ List<Object[]> rows = new ArrayList<>();
+ rows.add(new Object[] {"K1", d1, d4});
+ rows.add(new Object[] {"K1", d2, d3});
+ cache.setRowCache(rows);
+
+ HopTransformException exception =
+ assertThrows(HopTransformException.class, () ->
cache.validateRowsForSort("dim_customer"));
+ assertTrue(exception.getMessage().contains("overlapping validity
periods"));
+ assertTrue(exception.getMessage().contains("K1"));
+ }
+
+ @Test
+ void testValidateRowsForSortRejectsInvalidDateRange() {
+ DimensionCache cache = createKeyedCache();
+ Date d1 = timestamp(0);
+ Date d2 = timestamp(1);
+
+ List<Object[]> rows = new ArrayList<>();
+ rows.add(new Object[] {"K1", d2, d1});
+ cache.setRowCache(rows);
+
+ HopTransformException exception =
+ assertThrows(HopTransformException.class, () ->
cache.validateRowsForSort("dim_customer"));
+ assertTrue(exception.getMessage().contains("invalid date range"));
+ assertTrue(exception.getMessage().contains("K1"));
+ }
+
+ @Test
+ void testValidateRowsForSortRejectsNullOpenEndedOverlap() {
+ DimensionCache cache = createKeyedCache();
+ Date d2 = timestamp(1);
+ Date d4 = timestamp(3);
+
+ List<Object[]> rows = new ArrayList<>();
+ rows.add(new Object[] {"K1", null, d4});
+ rows.add(new Object[] {"K1", d2, null});
+ cache.setRowCache(rows);
+
+ HopTransformException exception =
+ assertThrows(HopTransformException.class, () ->
cache.validateRowsForSort("dim_customer"));
+ assertTrue(exception.getMessage().contains("overlapping validity
periods"));
+ }
+
+ @Test
+ void testIsZeroLengthValidityDetectsEqualDatesOnly() {
+ Date d1 = timestamp(0);
+ Date d2 = timestamp(1);
+
+ assertTrue(DimensionCache.isZeroLengthValidity(d1, d1));
+ assertFalse(DimensionCache.isZeroLengthValidity(d1, d2));
+ assertFalse(DimensionCache.isZeroLengthValidity(d2, d1));
+ assertFalse(DimensionCache.isZeroLengthValidity(null, d1));
+ assertFalse(DimensionCache.isZeroLengthValidity(d1, null));
+ }
+
+ @Test
+ void testExcludeZeroLengthValidityRowsRemovesOnlyEqualDateRows() {
+ IRowMeta rowMeta = createKeyedCache().getRowMeta();
+ Date d1 = timestamp(0);
+ Date d2 = timestamp(1);
+ Date d3 = timestamp(2);
+
+ List<Object[]> rows = new ArrayList<>();
+ rows.add(new Object[] {"K1", d1, d2});
+ rows.add(new Object[] {"K1", d2, d2});
+ rows.add(new Object[] {"K2", d3, d3});
+
+ List<Object[]> filtered =
DimensionCache.excludeZeroLengthValidityRows(rowMeta, rows, 1, 2);
+
+ assertEquals(1, filtered.size());
+ assertEquals("K1", filtered.get(0)[0]);
+ assertEquals(d1, filtered.get(0)[1]);
+ assertEquals(d2, filtered.get(0)[2]);
+ }
+
+ @Test
+ void testFilterZeroLengthRowsAllowsSortAfterRemovingPointRecords() {
+ DimensionCache cache = createKeyedCache();
+ IRowMeta rowMeta = cache.getRowMeta();
+ Date d1 = timestamp(0);
+ Date d2 = timestamp(1);
+ Date d3 = timestamp(2);
+ Date d4 = timestamp(3);
+
+ List<Object[]> rows = new ArrayList<>();
+ rows.add(new Object[] {"K1", d1, d4});
+ rows.add(new Object[] {"K1", d2, d2});
+
+ List<Object[]> filtered =
DimensionCache.excludeZeroLengthValidityRows(rowMeta, rows, 1, 2);
+ cache.setRowCache(filtered);
+
+ assertDoesNotThrow(() -> cache.validateRowsForSort("dim_customer"));
+ assertDoesNotThrow(cache::sortRows);
+ }
+
+ @Test
+ void testValidateRowsForSortAllowsDifferentNaturalKeys() {
+ DimensionCache cache = createKeyedCache();
+ Date d1 = timestamp(0);
+ Date d2 = timestamp(1);
+ Date d3 = timestamp(2);
+ Date d4 = timestamp(3);
+
+ List<Object[]> rows = new ArrayList<>();
+ rows.add(new Object[] {"K1", d1, d4});
+ rows.add(new Object[] {"K2", d2, d3});
+ cache.setRowCache(rows);
+
+ assertDoesNotThrow(() -> cache.validateRowsForSort("dim_customer"));
+ }
+
+ private static DimensionCache createKeyedCache() {
+ IRowMeta rowMeta = new RowMeta();
+ rowMeta.addValueMeta(new ValueMetaString("KEY"));
+ rowMeta.addValueMeta(new ValueMetaTimestamp("DATE_FROM"));
+ rowMeta.addValueMeta(new ValueMetaTimestamp("DATE_TO"));
+ return new DimensionCache(rowMeta, new int[] {0}, 1, 2);
+ }
+
+ private static Date timestamp(int hourOffset) {
+ long t0 = 1425300000000L;
+ return new Timestamp(t0 + 3600000L * hourOffset);
+ }
+
private static void assertCompareDateInterval(
DimensionCache dc, Object from1, Object to1, Object from2, Object to2,
int expectedValue) {
