DataFrame.Merge method incorrect behavior

[asmirnov82]

System information

• Any OS: Windows 10
• .NET Version (eg., dotnet --info): 5.0.201

Issue

• What did you do?:

I used DataFrame.Merge method for 2 dataframes

DataFrame merge = left.Merge(right, "Int", "Int");

Left dataframe:

Int Long
0 0
1 1

Right dataframe:

Int Long
null null

• What happened?

Incorrect merge results

Int_left Long_left Int_right Long_right
1 1 null null

• What did you expect?

I expect that row with Int 0 value behave the same way as row with int = null bevaior

Int_left Long_left Int_right Long_right
0 0 null null
1 1 null null

Source code / logs

Unit test to check:

[Fact]
        public void TestMerge_2()
        {
            DataFrame left = MakeDataFrameWithAllMutableColumnTypes(2, false);
            DataFrame right = MakeDataFrameWithAllMutableColumnTypes(1);

            DataFrame merge = left.Merge<int>(right, "Int", "Int");

            Assert.Equal(2, merge.Rows.Count);
            Assert.Equal(0, (int)merge.Columns["Int_left"][0]);
            Assert.Equal(1, (int)merge.Columns["Int_left"][1]);
        }

Please paste or attach the code or logs or traces that would be helpful to diagnose the issue you are reporting.

[pgovind]

I think you want to use Join here instead of merge. If you did the following:

left:
{Int Long
0   0   
1   1   
}
right:
{Int Long
null null
}

DataFrame join = left.Join(right);

join will look like what you expected:

Int_left  Long_left Int_right Long_right
0         0         null      null      
1         1         null      null      

[asmirnov82]

No, unfortunately DataFrame JOIN doesn't provide ability to use ON conidition. My final goal is to have something similar to SQL "a LEFT JOIN a ON a.field1 = b.field1 and a.field2 = b.field2". Merge allows to achive that at least on one column. However, during testing this functionality I noticed this strange behavior. In the description I provided very shortened example from DataFrame Unit tests. I played with MakeDataFrameWithAllMutableColumnTypes function and was very confused with the merge results.
For example:

DataFrame left = MakeDataFrameWithAllMutableColumnTypes(3, false);
DataFrame right = MakeDataFrameWithAllMutableColumnTypes(1);
DataFrame merge = left.Merge<int>(right, "Int", "Int");

left dataframe will be similar this:

0 0 ... 0
1 1 ... 1
2 2 ... 2

and right:
null ... null

Using JoinAlgorith,Left when I join something I would not expect that my rows from left dataframe will disappear. But currently I see the result as line with zeros goes away and merged dataframe looks like this:

1 1 ... 1 ... null null
2 2 ... 2 ... null null

I debugged a little and found that this happens, because of DefaultValue for int is 0. So, for me this place looks suspicious:

// First hash other dataframe on the rightJoinColumn 
...
Dictionary<TKey, ICollection<long>> multimap = otherColumn.GroupColumnValues<TKey>();

this code returns same map for 0 value in right row and null value right row. And then for some cases (I don't understand why) it doesn't match with null. If this behavoir is correct, could you please explain why 0 merging with nulls should goes away, but 1, 2, 3 and other values - not? It's absolutely unclear for me.

[pgovind]

this code returns same map for 0 value in right row and null value right row. And then for some cases (I don't understand why) it doesn't match with null

The problem here is that we are not differentiating between null and default values correctly. When this method was written, handling null values wasn't high priority, but I think it's time to fix it now. I'll put up a fix shortly.

[pgovind]

This will be fixed with https://github.com/dotnet/machinelearning/pull/5778/files