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 75fc6b37a2 fix dimension lookup/update key issue, fixes #8130 (#8177)
75fc6b37a2 is described below
commit 75fc6b37a2ae1eb04c41025d75b016c938ea3528
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Sat Aug 29 20:28:25 2026 +0200
fix dimension lookup/update key issue, fixes #8130 (#8177)
---
.../0044-dimension-lookup-numeric-key-tablemax.hpl | 248 ++++++++++++++++
.../database/0044-dimension-lookup-numeric-key.hpl | 248 ++++++++++++++++
.../main-0044-dimension-lookup-numeric-key.hwf | 325 +++++++++++++++++++++
.../scripts/script-dimension-numeric-key.sql | 65 +++++
.../dimensionlookup/DimensionLookup.java | 85 ++++--
.../DimensionLookupTechnicalKeyTest.java | 122 ++++++++
6 files changed, 1072 insertions(+), 21 deletions(-)
diff --git
a/integration-tests/database/0044-dimension-lookup-numeric-key-tablemax.hpl
b/integration-tests/database/0044-dimension-lookup-numeric-key-tablemax.hpl
new file mode 100644
index 0000000000..c564135eb9
--- /dev/null
+++ b/integration-tests/database/0044-dimension-lookup-numeric-key-tablemax.hpl
@@ -0,0 +1,248 @@
+<?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>0044-dimension-lookup-numeric-key-tablemax</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Issue #8130: technical key column is numeric(38,0) and is fed
by a sequence, so the generated key is a Long while the lookup metadata says
BigNumber.</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/08/29 09:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/08/29 09:00:00.000</modified_date>
+ </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>Assert technical key is usable</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Assert technical key is usable</from>
+ <to>OK</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Assert technical key is usable</from>
+ <to>Abort on unusable technical key</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>9</length>
+ <name>key</name>
+ <precision>0</precision>
+ <type>Integer</type>
+ </field>
+ <field>
+ <currency/>
+ <decimal/>
+ <set_empty_string>N</set_empty_string>
+ <format/>
+ <group/>
+ <length>-1</length>
+ <name>value</name>
+ <precision>-1</precision>
+ <type>String</type>
+ </field>
+ </fields>
+ <data>
+ <line>
+ <item>1</item>
+ <item>a</item>
+ </line>
+ <line>
+ <item>2</item>
+ <item>b</item>
+ </line>
+ <line>
+ <item>3</item>
+ <item>c</item>
+ </line>
+ <line>
+ <item>1</item>
+ <item>changed</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_numeric_tablemax</table>
+ <connection>unit-test-db</connection>
+ <commit>100</commit>
+ <update>Y</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>
+ <update>Insert</update>
+ </field>
+ <return>
+ <name>dimension_id</name>
+ <rename/>
+ <creation_method>tablemax</creation_method>
+ <use_autoinc>N</use_autoinc>
+ <version>version</version>
+ </return>
+ </fields>
+ <sequence/>
+ <min_year>1900</min_year>
+ <max_year>2199</max_year>
+ <cache_size>0</cache_size>
+ <preload_cache>N</preload_cache>
+ <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>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <type>FilterRows</type>
+ <name>Assert technical key is usable</name>
+ <description>The output field is declared as an Integer, so reading it as
one must work for every row.</description>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <operator>-</operator>
+ <leftvalue>dimension_id</leftvalue>
+ <function>></function>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>0</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ <conditions/>
+ </condition>
+ </compare>
+ <send_true_to>OK</send_true_to>
+ <send_false_to>Abort on unusable technical key</send_false_to>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>576</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>Dummy</type>
+ <name>OK</name>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>768</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>Abort</type>
+ <name>Abort on unusable technical key</name>
+ <row_threshold>0</row_threshold>
+ <always_log_rows>Y</always_log_rows>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>768</xloc>
+ <yloc>224</yloc>
+ </GUI>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/database/0044-dimension-lookup-numeric-key.hpl
b/integration-tests/database/0044-dimension-lookup-numeric-key.hpl
new file mode 100644
index 0000000000..374d6f7822
--- /dev/null
+++ b/integration-tests/database/0044-dimension-lookup-numeric-key.hpl
@@ -0,0 +1,248 @@
+<?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>0044-dimension-lookup-numeric-key</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Issue #8130: technical key column is numeric(38,0) and is fed
by a sequence, so the generated key is a Long while the lookup metadata says
BigNumber.</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/08/29 09:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/08/29 09:00:00.000</modified_date>
+ </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>Assert technical key is usable</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Assert technical key is usable</from>
+ <to>OK</to>
+ <enabled>Y</enabled>
+ </hop>
+ <hop>
+ <from>Assert technical key is usable</from>
+ <to>Abort on unusable technical key</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>9</length>
+ <name>key</name>
+ <precision>0</precision>
+ <type>Integer</type>
+ </field>
+ <field>
+ <currency/>
+ <decimal/>
+ <set_empty_string>N</set_empty_string>
+ <format/>
+ <group/>
+ <length>-1</length>
+ <name>value</name>
+ <precision>-1</precision>
+ <type>String</type>
+ </field>
+ </fields>
+ <data>
+ <line>
+ <item>1</item>
+ <item>a</item>
+ </line>
+ <line>
+ <item>2</item>
+ <item>b</item>
+ </line>
+ <line>
+ <item>3</item>
+ <item>c</item>
+ </line>
+ <line>
+ <item>1</item>
+ <item>changed</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_numeric_key</table>
+ <connection>unit-test-db</connection>
+ <commit>100</commit>
+ <update>Y</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>
+ <update>Insert</update>
+ </field>
+ <return>
+ <name>dimension_id</name>
+ <rename/>
+ <creation_method>sequence</creation_method>
+ <use_autoinc>N</use_autoinc>
+ <version>version</version>
+ </return>
+ </fields>
+ <sequence>dimension_numeric_key_seq</sequence>
+ <min_year>1900</min_year>
+ <max_year>2199</max_year>
+ <cache_size>0</cache_size>
+ <preload_cache>N</preload_cache>
+ <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>96</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <type>FilterRows</type>
+ <name>Assert technical key is usable</name>
+ <description>The output field is declared as an Integer, so reading it as
one must work for every row.</description>
+ <compare>
+ <condition>
+ <negated>N</negated>
+ <operator>-</operator>
+ <leftvalue>dimension_id</leftvalue>
+ <function>></function>
+ <value>
+ <name>constant</name>
+ <type>Integer</type>
+ <text>0</text>
+ <length>-1</length>
+ <precision>0</precision>
+ <isnull>N</isnull>
+ <mask>####0;-####0</mask>
+ </value>
+ <conditions/>
+ </condition>
+ </compare>
+ <send_true_to>OK</send_true_to>
+ <send_false_to>Abort on unusable technical key</send_false_to>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>576</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>Dummy</type>
+ <name>OK</name>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>768</xloc>
+ <yloc>96</yloc>
+ </GUI>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform>
+ <type>Abort</type>
+ <name>Abort on unusable technical key</name>
+ <row_threshold>0</row_threshold>
+ <always_log_rows>Y</always_log_rows>
+ <abort_option>ABORT_WITH_ERROR</abort_option>
+ <distribute>Y</distribute>
+ <copies>1</copies>
+ <GUI>
+ <xloc>768</xloc>
+ <yloc>224</yloc>
+ </GUI>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git
a/integration-tests/database/main-0044-dimension-lookup-numeric-key.hwf
b/integration-tests/database/main-0044-dimension-lookup-numeric-key.hwf
new file mode 100644
index 0000000000..a583b58e8b
--- /dev/null
+++ b/integration-tests/database/main-0044-dimension-lookup-numeric-key.hwf
@@ -0,0 +1,325 @@
+<?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-0044-dimension-lookup-numeric-key</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description>Issue #8130: Dimension lookup/update against a numeric(38,0)
technical key. The key column reads back as a BigNumber while every generator
hands back a Long, so both generator methods that can feed one are
covered.</description>
+ <extended_description/>
+ <workflow_version/>
+ <created_user>-</created_user>
+ <created_date>2026/08/29 09:00:00.000</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2026/08/29 09: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>scripts/script-dimension-numeric-key.sql</name>
+ <description/>
+ <type>SQL</type>
+ <attributes/>
+ <connection>unit-test-db</connection>
+ <sendOneStatement>N</sendOneStatement>
+ <sql/>
+
<sqlfilename>${PROJECT_HOME}/scripts/script-dimension-numeric-key.sql</sqlfilename>
+ <sqlfromfile>Y</sqlfromfile>
+ <useVariableSubstitution>N</useVariableSubstitution>
+ <parallel>N</parallel>
+ <xloc>208</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0044-dimension-lookup-numeric-key.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+
<filename>${PROJECT_HOME}/0044-dimension-lookup-numeric-key.hpl</filename>
+ <logext/>
+ <logfile/>
+ <loglevel>Basic</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>384</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>5 rows from the sequence</name>
+ <description/>
+ <type>EVAL_TABLE_CONTENT</type>
+ <attributes/>
+ <add_rows_result>N</add_rows_result>
+ <clear_result_rows>Y</clear_result_rows>
+ <connection>unit-test-db</connection>
+ <custom_sql></custom_sql>
+ <is_custom_sql>N</is_custom_sql>
+ <is_usevars>N</is_usevars>
+ <limit>5</limit>
+ <schemaname>public</schemaname>
+ <success_condition>rows_count_equal</success_condition>
+ <tablename>dimension_numeric_key</tablename>
+ <parallel>N</parallel>
+ <xloc>560</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>4 generated keys from the sequence</name>
+ <description/>
+ <type>EVAL_TABLE_CONTENT</type>
+ <attributes/>
+ <add_rows_result>N</add_rows_result>
+ <clear_result_rows>Y</clear_result_rows>
+ <connection>unit-test-db</connection>
+ <custom_sql>SELECT 1
+FROM public.dimension_numeric_key
+WHERE dimension_id > 0
+</custom_sql>
+ <is_custom_sql>Y</is_custom_sql>
+ <is_usevars>N</is_usevars>
+ <limit>4</limit>
+ <schemaname>public</schemaname>
+ <success_condition>rows_count_equal</success_condition>
+ <tablename>dimension_numeric_key</tablename>
+ <parallel>N</parallel>
+ <xloc>736</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>2 versions for key 1 from the sequence</name>
+ <description/>
+ <type>EVAL_TABLE_CONTENT</type>
+ <attributes/>
+ <add_rows_result>N</add_rows_result>
+ <clear_result_rows>Y</clear_result_rows>
+ <connection>unit-test-db</connection>
+ <custom_sql>SELECT 1
+FROM public.dimension_numeric_key
+WHERE "key" = 1
+</custom_sql>
+ <is_custom_sql>Y</is_custom_sql>
+ <is_usevars>N</is_usevars>
+ <limit>2</limit>
+ <schemaname>public</schemaname>
+ <success_condition>rows_count_equal</success_condition>
+ <tablename>dimension_numeric_key</tablename>
+ <parallel>N</parallel>
+ <xloc>912</xloc>
+ <yloc>48</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>0044-dimension-lookup-numeric-key-tablemax.hpl</name>
+ <description/>
+ <type>PIPELINE</type>
+ <attributes/>
+ <add_date>N</add_date>
+ <add_time>N</add_time>
+ <clear_files>N</clear_files>
+ <clear_rows>N</clear_rows>
+ <create_parent_folder>N</create_parent_folder>
+ <exec_per_row>N</exec_per_row>
+
<filename>${PROJECT_HOME}/0044-dimension-lookup-numeric-key-tablemax.hpl</filename>
+ <logext/>
+ <logfile/>
+ <loglevel>Basic</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>384</xloc>
+ <yloc>176</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>5 rows from the table maximum</name>
+ <description/>
+ <type>EVAL_TABLE_CONTENT</type>
+ <attributes/>
+ <add_rows_result>N</add_rows_result>
+ <clear_result_rows>Y</clear_result_rows>
+ <connection>unit-test-db</connection>
+ <custom_sql></custom_sql>
+ <is_custom_sql>N</is_custom_sql>
+ <is_usevars>N</is_usevars>
+ <limit>5</limit>
+ <schemaname>public</schemaname>
+ <success_condition>rows_count_equal</success_condition>
+ <tablename>dimension_numeric_tablemax</tablename>
+ <parallel>N</parallel>
+ <xloc>560</xloc>
+ <yloc>176</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>4 generated keys from the table maximum</name>
+ <description/>
+ <type>EVAL_TABLE_CONTENT</type>
+ <attributes/>
+ <add_rows_result>N</add_rows_result>
+ <clear_result_rows>Y</clear_result_rows>
+ <connection>unit-test-db</connection>
+ <custom_sql>SELECT 1
+FROM public.dimension_numeric_tablemax
+WHERE dimension_id > 0
+</custom_sql>
+ <is_custom_sql>Y</is_custom_sql>
+ <is_usevars>N</is_usevars>
+ <limit>4</limit>
+ <schemaname>public</schemaname>
+ <success_condition>rows_count_equal</success_condition>
+ <tablename>dimension_numeric_tablemax</tablename>
+ <parallel>N</parallel>
+ <xloc>736</xloc>
+ <yloc>176</yloc>
+ <attributes_hac/>
+ </action>
+ <action>
+ <name>2 versions for key 1 from the table maximum</name>
+ <description/>
+ <type>EVAL_TABLE_CONTENT</type>
+ <attributes/>
+ <add_rows_result>N</add_rows_result>
+ <clear_result_rows>Y</clear_result_rows>
+ <connection>unit-test-db</connection>
+ <custom_sql>SELECT 1
+FROM public.dimension_numeric_tablemax
+WHERE "key" = 1
+</custom_sql>
+ <is_custom_sql>Y</is_custom_sql>
+ <is_usevars>N</is_usevars>
+ <limit>2</limit>
+ <schemaname>public</schemaname>
+ <success_condition>rows_count_equal</success_condition>
+ <tablename>dimension_numeric_tablemax</tablename>
+ <parallel>N</parallel>
+ <xloc>912</xloc>
+ <yloc>176</yloc>
+ <attributes_hac/>
+ </action>
+ </actions>
+ <hops>
+ <hop>
+ <from>Start</from>
+ <to>scripts/script-dimension-numeric-key.sql</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>Y</unconditional>
+ </hop>
+ <hop>
+ <from>scripts/script-dimension-numeric-key.sql</from>
+ <to>0044-dimension-lookup-numeric-key.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0044-dimension-lookup-numeric-key.hpl</from>
+ <to>5 rows from the sequence</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>5 rows from the sequence</from>
+ <to>4 generated keys from the sequence</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>4 generated keys from the sequence</from>
+ <to>2 versions for key 1 from the sequence</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>2 versions for key 1 from the sequence</from>
+ <to>0044-dimension-lookup-numeric-key-tablemax.hpl</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>0044-dimension-lookup-numeric-key-tablemax.hpl</from>
+ <to>5 rows from the table maximum</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>5 rows from the table maximum</from>
+ <to>4 generated keys from the table maximum</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ <hop>
+ <from>4 generated keys from the table maximum</from>
+ <to>2 versions for key 1 from the table maximum</to>
+ <enabled>Y</enabled>
+ <evaluation>Y</evaluation>
+ <unconditional>N</unconditional>
+ </hop>
+ </hops>
+ <notepads>
+ </notepads>
+ <attributes/>
+</workflow>
diff --git
a/integration-tests/database/scripts/script-dimension-numeric-key.sql
b/integration-tests/database/scripts/script-dimension-numeric-key.sql
new file mode 100644
index 0000000000..0b24aefaf3
--- /dev/null
+++ b/integration-tests/database/scripts/script-dimension-numeric-key.sql
@@ -0,0 +1,65 @@
+/*
+ * 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.
+ *
+ */
+
+/* Issue #8130: a dimension whose technical key is numeric(38,0), fed by a
sequence. */
+
+DROP TABLE IF EXISTS public.dimension_numeric_key
+;
+
+DROP SEQUENCE IF EXISTS public.dimension_numeric_key_seq
+;
+
+CREATE SEQUENCE public.dimension_numeric_key_seq
+;
+
+CREATE TABLE "public".dimension_numeric_key
+(
+ dimension_id NUMERIC(38,0) NOT NULL
+ , version INTEGER
+ , date_from TIMESTAMP
+ , date_to TIMESTAMP
+ , "key" NUMERIC(38,0)
+ , "value" TEXT
+)
+;
+
+CREATE INDEX idx_dimension_numeric_key_lookup ON
"public".dimension_numeric_key("key")
+;
+
+CREATE INDEX idx_dimension_numeric_key_tk ON
"public".dimension_numeric_key(dimension_id)
+;
+
+DROP TABLE IF EXISTS public.dimension_numeric_tablemax
+;
+
+CREATE TABLE "public".dimension_numeric_tablemax
+(
+ dimension_id NUMERIC(38,0) NOT NULL
+ , version INTEGER
+ , date_from TIMESTAMP
+ , date_to TIMESTAMP
+ , "key" NUMERIC(38,0)
+ , "value" TEXT
+)
+;
+
+CREATE INDEX idx_dimension_numeric_tablemax_lookup ON
"public".dimension_numeric_tablemax("key")
+;
+
+CREATE INDEX idx_dimension_numeric_tablemax_tk ON
"public".dimension_numeric_tablemax(dimension_id)
+;
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 2f3b38e3b4..032338ab4a 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
@@ -471,6 +471,44 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
}
}
+ /**
+ * A technical key described the way the lookup's own row metadata describes
it.
+ *
+ * <p>That metadata comes from the key column, so it says what a SELECT
reads back: a
+ * numeric(38,0) key reads back as a BigNumber. A generated key is a Long
whatever the column is,
+ * whether it came from a sequence, from the table maximum, or from the
driver's generated keys,
+ * so the two disagree the moment a row is inserted. Everything downstream
of the insert reads the
+ * row through this metadata, the cache included, because it serialises with
a clone of it. Issue
+ * #8130.
+ */
+ private Object asLookupType(Object technicalKey) throws HopValueException {
+ if (data.returnRowMeta == null || data.returnRowMeta.isEmpty()) {
+ return technicalKey;
+ }
+ return asType(data.returnRowMeta.getValueMeta(0), technicalKey);
+ }
+
+ /**
+ * A value described the way the given metadata describes it.
+ *
+ * <p>How the value is read is decided by what it actually is, not by what
any row metadata
+ * claims. That is the point: the disagreement between the two is exactly
what this repairs, so
+ * taking either side's word for it would reintroduce the fault.
+ *
+ * <p>Static and package private so the conversion can be tested on its own:
this is the part that
+ * used to throw, and it should not take a database to find that out again.
+ */
+ static Object asType(IValueMeta targetMeta, Object value) throws
HopValueException {
+ if (value == null || targetMeta == null) {
+ return value;
+ }
+ IValueMeta actualMeta = ValueMetaFactory.guessValueMetaInterface(value);
+ if (actualMeta == null || actualMeta.getType() == targetMeta.getType()) {
+ return value;
+ }
+ return targetMeta.convertData(actualMeta, value);
+ }
+
private synchronized Object[] lookupValues(IRowMeta rowMeta, Object[] row)
throws HopException {
DLFields f = meta.getFields();
@@ -586,7 +624,7 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
if (!meta.isUpdate()) {
if (returnRow == null) {
returnRow = new Object[data.returnRowMeta.size()];
- returnRow[0] = data.notFoundTk;
+ returnRow[0] = asLookupType(data.notFoundTk);
if (meta.getCacheSize() >= 0) { // need -oo to +oo as well...
returnRow[returnRow.length - 2] = data.minDate;
@@ -672,14 +710,15 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
* row.fieldnrs)
*/
technicalKey =
- dimInsert(
- data.inputRowMeta,
- row,
- technicalKey,
- true,
- valueVersion,
- valueDateFrom,
- valueDateTo);
+ asLookupType(
+ dimInsert(
+ data.inputRowMeta,
+ row,
+ technicalKey,
+ true,
+ valueVersion,
+ valueDateFrom,
+ valueDateTo));
incrementLinesOutput();
returnRow = new Object[data.returnRowMeta.size()];
@@ -904,8 +943,15 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
// update our technicalKey with the return of the insert
technicalKey =
- dimInsert(
- rowMeta, row, technicalKey, false, valueNewVersion,
valueDateFrom, valueDateTo);
+ asLookupType(
+ dimInsert(
+ rowMeta,
+ row,
+ technicalKey,
+ false,
+ valueNewVersion,
+ valueDateFrom,
+ valueDateTo));
incrementLinesOutput();
// We need to capture this change in the cache as well...
@@ -954,16 +1000,13 @@ public class DimensionLookup extends
BaseTransform<DimensionLookupMeta, Dimensio
// Then the technical key...
//
- IValueMeta tkValueMeta = data.returnRowMeta.getValueMeta(0);
- if (data.returnRowMeta.getValueMeta(0).isBigNumber() && returnRow[0]
instanceof Long) {
- if (isDebug()) {
- logDebug("Changing the type of the technical key from TYPE_BIGNUMBER
to an TYPE_INTEGER");
- }
- data.returnRowMeta.setValueMeta(
- 0, ValueMetaFactory.cloneValueMeta(tkValueMeta,
IValueMeta.TYPE_INTEGER));
- }
- // Get the technical key after lookup
- outputRow[outputIndex++] =
tkValueMeta.getNativeDataType(returnRow[inputIndex++]);
+ // The output row declares what the technical key is -- an Integer for the
generated methods,
+ // a String for UUID, the source field's type for FIELD -- and that
declaration is what the
+ // rest of the pipeline reads. Convert to it rather than passing on
whatever the lookup
+ // happened to return, which for a numeric(38,0) key column is a
BigDecimal. Issue #8130.
+ outputRow[outputIndex] =
+ asType(data.outputRowMeta.getValueMeta(outputIndex),
returnRow[inputIndex++]);
+ outputIndex++;
// skip the version in the input
inputIndex++;
diff --git
a/plugins/transforms/dimensionlookup/src/test/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupTechnicalKeyTest.java
b/plugins/transforms/dimensionlookup/src/test/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupTechnicalKeyTest.java
new file mode 100644
index 0000000000..21d4f0a511
--- /dev/null
+++
b/plugins/transforms/dimensionlookup/src/test/java/org/apache/hop/pipeline/transforms/dimensionlookup/DimensionLookupTechnicalKeyTest.java
@@ -0,0 +1,122 @@
+/*
+ * 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.dimensionlookup;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertSame;
+import static org.junit.jupiter.api.Assertions.assertThrows;
+
+import java.math.BigDecimal;
+import org.apache.hop.core.exception.HopValueException;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.core.row.value.ValueMetaBigNumber;
+import org.apache.hop.core.row.value.ValueMetaBinary;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.junit.jupiter.api.Test;
+
+/**
+ * The technical key travels between two descriptions that need not agree: the
key column decides
+ * what a lookup reads back, while every key generator hands back a Long.
Issue #8130 was a cast
+ * across that gap, so these cover the conversion that replaced it without
needing a database.
+ */
+class DimensionLookupTechnicalKeyTest {
+
+ /**
+ * Issue #8130. A sequence, the table maximum and the driver's generated
keys all produce a Long,
+ * whatever the key column is. Storing one in a row described by a
numeric(38,0) column used to
+ * throw ClassCastException.
+ */
+ @Test
+ void aGeneratedLongIsStoredAsTheKeyColumnDescribesIt() throws
HopValueException {
+ Object stored = DimensionLookup.asType(new
ValueMetaBigNumber("dimension_id", 38, 0), 42L);
+
+ assertEquals(BigDecimal.valueOf(42), stored);
+ }
+
+ /**
+ * The other direction, and the one the rest of the pipeline sees: the
output row declares the
+ * technical key an Integer, so a key read back from a numeric column has to
arrive as one.
+ */
+ @Test
+ void aKeyReadFromANumericColumnIsHandedOnAsTheDeclaredInteger() throws
HopValueException {
+ Object handedOn =
+ DimensionLookup.asType(new ValueMetaInteger("dimension_id"), new
BigDecimal("42"));
+
+ assertEquals(42L, handedOn);
+ }
+
+ @Test
+ void aValueThatAlreadyMatchesIsPassedThroughUntouched() throws
HopValueException {
+ Long key = 42L;
+
+ assertSame(key, DimensionLookup.asType(new
ValueMetaInteger("dimension_id"), key));
+ }
+
+ /** The UUID creation method: the key is a String on both sides and must
stay one. */
+ @Test
+ void aUuidKeyIsNotConverted() throws HopValueException {
+ String uuid = "00000000-0000-0000-0000-000000000000";
+
+ assertSame(uuid, DimensionLookup.asType(new
ValueMetaString("dimension_id", 36, 0), uuid));
+ }
+
+ /** The field creation method against a binary key column, which is what
0012-4 exercises. */
+ @Test
+ void aBinaryKeyIsNotConverted() throws HopValueException {
+ byte[] key = new byte[] {1, 2, 3};
+
+ assertSame(key, DimensionLookup.asType(new
ValueMetaBinary("dimension_id"), key));
+ }
+
+ @Test
+ void aMissingKeyStaysMissing() throws HopValueException {
+ assertNull(DimensionLookup.asType(new ValueMetaBigNumber("dimension_id",
38, 0), null));
+ }
+
+ @Test
+ void aValueWithNothingToConvertToIsLeftAlone() throws HopValueException {
+ Long key = 42L;
+
+ assertSame(key, DimensionLookup.asType(null, key));
+ }
+
+ /**
+ * A value Hop has no description for is handed on rather than guessed at:
converting it would
+ * mean inventing a type for it, and being wrong about that is how this bug
started.
+ */
+ @Test
+ void aValueHopCannotDescribeIsLeftAlone() throws HopValueException {
+ Object opaque = new Object();
+
+ assertSame(opaque, DimensionLookup.asType(new
ValueMetaInteger("dimension_id"), opaque));
+ }
+
+ /**
+ * Why the conversion has to describe the value by what it is. Asking the
key column's own
+ * metadata to read a generated Long is the call that threw in issue #8130,
so a later
+ * simplification back to it would bring the fault back with it.
+ */
+ @Test
+ void theKeyColumnsOwnMetadataCannotReadAGeneratedLong() {
+ IValueMeta keyColumn = new ValueMetaBigNumber("dimension_id", 38, 0);
+
+ assertThrows(HopValueException.class, () ->
keyColumn.getNativeDataType(42L));
+ }
+}
