Join stage behavior/bug in 11.3?

[pavankvk]

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?

[pavankvk]

Forgot to mention that fields are defined as Nullable, so values should be set to NULL

[ray.wurlod]

Are all columns on the right input (as well as output columns derived from them) set nullable?

[pavankvk]

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?

[ray.wurlod]

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.

[chulett]

which behavior is correct 8.1 or 11.3?

Short answer? 11.3