mycloudy127 opened a new issue, #8130:
URL: https://github.com/apache/hop/issues/8130
### Apache Hop version?
2.19.0
### Java version?
OpenJDK Corretto 21.0.12.8.1 (Java 21.0.12 LTS, 64-bit)
### Operating system
Windows
### What happened?
## Problem
The **Dimension Lookup/Update** transform fails when it inserts a new
dimension row into PostgreSQL and the technical-key column is `numeric(38,0)`.
Our existing PDI implementation and DW schema use PostgreSQL sequences for
technical keys and `numeric(38,0)` for the key columns. Changing the database
schema is not an option for us.
Hop detects the technical key as `BigNumber(38)`, while PostgreSQL
`nextval()` returns a Java `Long`. When a new dimension row is required, the
transform fails with a `ClassCastException`.
Pipelines with no source rows, or rows that do not require a new dimension
record, can complete successfully. The failure occurs consistently when a new
technical key must be generated.
## Expected behavior
Dimension Lookup/Update should insert the new row successfully and return
the generated sequence value, converting the PostgreSQL sequence `Long`
correctly for a `numeric(38,0)` technical-key column.
## Actual behavior
The transform fails with:
```text
Unexpected conversion error while converting value [variant_domains_key
BigNumber(38)] to a BigNumber
Caused by: java.lang.ClassCastException
Stack trace location:
org.apache.hop.core.row.value.ValueMetaBase.getBigNumber(...)
org.apache.hop.core.row.value.ValueMetaBigNumber.getNativeDataType(...)
org.apache.hop.pipeline.transforms.dimensionlookup.DimensionLookup.lookupValues(DimensionLookup.java:966)
Minimal reproduction
1. Use a PostgreSQL connection.
2. Create a table with a numeric(38,0) technical key and a sequence:
create sequence dim_test_seq;
create table dim_test (
test_key numeric(38,0) primary key,
business_key numeric(38,0) not null
);
3. Create a pipeline with a Dimension Lookup/Update transform:
- Technical key field: test_key
- Technical key creation method: Sequence
- Sequence name: dim_test_seq
- Business/natural key: business_key
4. Send a row with a new business_key value so that the transform must
insert a row.
5. Run the pipeline.
The pipeline fails while converting the generated sequence value.
Additional observation
The installed Hop 2.19 Dimension Lookup code appears to detect the
BigNumber/Long case and replace the return metadata with an Integer metadata
type. However, it subsequently performs conversion using the previous BigNumber
metadata, causing the failure.
Please let me know if a sample Hop pipeline or a full sanitized stack trace
would help.
```
### Issue Priority
Priority: 3
### Issue Component
Component: Transforms
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
