[Bug]: Inconsistent meta type handling results into casting exceptions when using SQLite and Database join

[vdwals]

Apache Hop version?

2.7.0 (2023-11-17 12.19.09)

Java version?

openjdk version "17.0.3" 2022-04-19

Operating system

Windows

What happened?

I execute the attached example with a database join accessing an SQLite source. When checking the preview of the join step, the lookup columns are displayed as null and an error message indicates inconsistent meta type of those fields.
mini-example-issue-2312.zip

I discovered this in the context of #2312 and have already come across such issue in three different cases. Anyhow I will track them separately until either separate or a common solution is found.

Issue Priority

Priority: 2

Issue Component

Component: Transforms

[vdwals]

.take-issue

[vdwals]

It seems the JDBC driver returns for the result column type: NUMERIC. This is mapped to hop.Number, expecting java.lang.Double. But the result row is returned with a java.lang.Long.
I can solve it in different ways:

1. Try to overwrite the detection of hop.Number incase precision and length equal 0 and it is sqlite variant (my current case), I set meta to hop.Integer, being compatible to java.lang.Long. (Efficient, might be to simple and have side effects, does not solve all other constellations like Sort Transform or Pipeline execution finish)
2. During copying of the JDBC result rows, the specific case is checked: Meta type is hop.Number, object type is java.lang.Long -> Convert to java.lang.Double. (Would handle exactly this one case, impacting the overall performance of databasejoin slightly)
3. In BaseTransform.putRow() I could add logic to align object type with row meta type (Might fix all detected issues, has performance impact on nearly all Transforms)

Additionally, in ValueMetaBase.convertNumberToString I changed the expected type from Double to Number as this is compatible to the related functions and more generic, therefore avoiding the specific error message.

Further testing showed that option 1 is not working and could cut off decimals. So it's 2 or 3. I will check in option 2 until I discovered the related issued mentioned in the description. Those might result into 3 beeing the best solution.
Let me know about your opinion.