Hash partition with difference in numeric key columns

[le thuong]

I encounter the following issue on a join stage on 5 columns (3 varchar, 2 numeric). The 2 inputs are sorted and hash partitioned on the 5 columns. For a given combination of the 5 key columns, we were expecting a match between the 2 inputs, and Datastage job did not return a match.

I found out that the issue is due to the fact that the numeric data types in the left input were not equal to the numeric data types of the right input (Dec 10,4 vs Dec 38,10 if I am correct), even if the numeric values were equal.

The solution was to convert the numeric data types to get the same on the 2 inputs.

Did someone meet the same issue ?

This means that applying Hash partition to a number can give a different value depending on the data type and not only on the value of the number ?

[priyadarshikunal]

You correctly found the issue, Yes the hash calculation takes the data type in to account as well, hence the issue. Even if the extended property of a varchar (unicode) is set it will have different hash than that of a varchar.

[ray.wurlod]

That said, it is working as documented.