Hi
We are migrating to 11.3, and noticed a bug/strange behavior for the join stage. Scenario is a left outer join, unmatched records from the right stream should have NULL values after the join. in 8.X the behavior is as expected, its set to NULL and we can trap those records with a IsNull.
In 11.3, the values are beign defaulated instead of NUll. For example date fiellds from the right stream are set to 0001-01-01 and Varchar fields set to empty string as opposed to NULL.
Did anyone come across this? Is there a patch that we missed?
Join stage behavior/bug in 11.3?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
the update is now we are not sure if its a bug in 11.3 or 8.1..
the particular field on right input of join is not nullable(the value is hard coded), on output link its nullable, 8.1 behavior is it sets it to NULL for unmatched records. this i think should be the idal behavior because the column is expected to become null for unmatched records. 8.1 sends it out as null.
in 11.3
the same job, sets the output to a default value. if the right input is changed to be nullable, then it behaves the same way as 8.1
So now the question is, should join behave based on the input nullable property or set the value to null, based on the join outcome. which behavior is correct 8.1 or 11.3?
the particular field on right input of join is not nullable(the value is hard coded), on output link its nullable, 8.1 behavior is it sets it to NULL for unmatched records. this i think should be the idal behavior because the column is expected to become null for unmatched records. 8.1 sends it out as null.
in 11.3
the same job, sets the output to a default value. if the right input is changed to be nullable, then it behaves the same way as 8.1
So now the question is, should join behave based on the input nullable property or set the value to null, based on the join outcome. which behavior is correct 8.1 or 11.3?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
For a left outer join all columns from the right input may be null and therefore must be nullable.
To be transferred to the output without default values being invoked, those output columns must also be nullable.
If that wasn't enforced in version 8.1 it should have been.
To be transferred to the output without default values being invoked, those output columns must also be nullable.
If that wasn't enforced in version 8.1 it should have been.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.