[Bug]: The "Database Lookup" step trim function does not seem to work

[duursma]

Apache Hop version?

2.4.0

Java version?

11

Operating system

Windows

What happened?

I select a number of records from a database with a field know to have trailing spaces/newlines. After enabeling 'trim - both' on the field the trailing whitespace remains. After including a "String operation" step with the trim option set to 'both' the trailing whitespace does disappear.

Issue Priority

Priority: 3

Issue Component

Component: Database

[nadment]

In which transform you put 'trim - both' on the field?
Can you provide a basic pipeline to illustrate the problem?

[duursma]

test-trim.hpl.gz

[liosc]

Hi. I just tried a very similar pipeline and the database lookup trim worked. My environment is Hop 2.4.0 (March, 26 snapshot), Windows and jdk-11.0.15.10. Could you please share the exact version of Java and the stream metadata after the look up (you can add a "Metadata structure of stream" transform and share the output of that).

[duursma]

My environment is: hop 2.4.0 (2023-03-27 07.51.24)
openjdk version "19.0.1" 2022-10-18
OpenJDK Runtime Environment (build 19.0.1+10-21)
OpenJDK 64-Bit Server VM (build 19.0.1+10-21, mixed mode, sharing)
ojdbc8-21.4.0.0.1.jar
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0

Metadata output:
Position Fieldname Comments Type Length Precision Origin
2 REMARK String -1 -1 Database lookup
1 SAMPLE_NR SAMPLE_NR String 13 -1 Table input

Two sample lines from the database input:
SAMPLE_NR REMARK
L1995-06-7019 MON_DAT is analysedatum. Feitelijke MON_DAT is onbekend

L1995-06-7018 MON_DAT is analysedatum. Feitelijke MON_DAT is onbekend

Two sample lines from the database input:
SAMPLE_NR REMARK
L1995-06-7019 MON_DAT is analysedatum. Feitelijke MON_DAT is onbekend
L1995-06-7018 MON_DAT is analysedatum. Feitelijke MON_DAT is onbekend

So if copy/paste works the same, it looks newline related. The two trims do not do the same function with respect to newlines.

[sramazzina]

I had a look at the code and I confirm that the two transforms does the same thing in a different way. Apart from this, regarding the newline character in the output, that could be acceptable so I'm not conridering the behavior as a bug. The idea could be to add a new option parameter to let the user decide if the newline char must be removed by the trim or not. In that case we can change the type of the issue from bug to feature request. @hansva what do you think?

[duursma]

You could also make it a documentation issue. Make clear what trimming does in each case. But I would prefer to have 'trim' have a single meaning everywhere.

[sramazzina]

@duursma I agree with you and we will take care about that but, apart from this techincal detail, we must decide how to manage the newline char. Someone could disaagree about removing the newline char, someone else could agree (like you for example). Who knows. Therefore the idea of providing a configuration option to guide that choice seems to be the best one.

[hansva]

I disagree with adding an option. Trim should be as simple as a java String.Trim() and should be uniform everywhere.