BUG: Left join on index and column gives incorrect output

[dsaxton]

import numpy as np
import pandas as pd

df_left = pd.DataFrame(index=["a", "b"])
df_right = pd.DataFrame({"x": ["a", "c"]})

pd.merge(df_left, df_right, left_index=True, right_on="x", how="left")
#      x
# 0.0  a
# NaN  b

Problem description

This is closely related to #28220 but deals with the values of the DataFrame rather than the index itself. When left joining on an index and a column it looks like the value "b" from the index of df_left is somehow getting carried over to the column x, but "a" should be the only value in this column since it's the only one that matches the index from df_left. This is happening on 0.25.1 and master, and has been a bug for some time according to #28220.

Expected Output

     x
a    a
b  NaN

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit : None python : 3.7.3.final.0 python-bits : 64 OS : Darwin OS-release : 18.7.0 machine : x86_64 processor : i386 byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : en_US.UTF-8

pandas : 0.25.1
numpy : 1.16.4
pytz : 2019.2
dateutil : 2.8.0
pip : 19.1.1
setuptools : 41.0.1
Cython : None
pytest : 5.0.1
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 7.7.0
pandas_datareader: None
bs4 : None
bottleneck : None
fastparquet : None
gcsfs : None
lxml.etree : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
xarray : None
xlrd : None
xlwt : None
xlsxwriter : None

Seems related to this issue: #17257

[phofl]

@dsaxton I would expect the same output as you described. But the function _maybe_add_join_keys adds the values again after the join worked as intended. The column x had the values [a, nan] before this function and [a, b] afterwards.

I found a few tests covering exactly this behavior.
For example the test https://github.com/pandas-dev/pandas/blob/master/pandas/tests/reshape/merge/test_merge.py#L1285 in case of right joins I would expect the output

        expected = pd.DataFrame(
            [
                [0, 0, 0],
                [1, 1, 1],
                [2, 2, 2],
                [np.nan, np.nan, 3],
                [np.nan, np.nan, 4],
                [np.nan, np.nan, 5],
            ],
            columns=["a", "key", "b"],
        )

instead of

        expected = pd.DataFrame(
            [
                [0, 0, 0],
                [1, 1, 1],
                [2, 2, 2],
                [np.nan, 3, 3],
                [np.nan, 4, 4],
                [np.nan, 5, 5],
            ],
            columns=["a", "key", "b"],
        )

I think that I have found a fix for the index issue, but the value issue is unrelated.

[dsaxton]

@phofl Yeah I think I recall the _maybe_add_join_keys function and not really understanding why it was needed

[phofl]

@dsaxton Thanks very much. I will push a PR shortly, but I think we have to talk about the expected behavior, because that is not 100 percent clear to me.

I will look into _maybe_add_join_keys after this