Merge with Categorical as join key + how='inner' returns wrong output

[TartySG]

Code Sample, a copy-pastable example if possible

df = pd.DataFrame([['A', 1],  
                   ['B', 2], 
                   ['B', 3], 
                   ['A', 4]], columns=['Col1', 'Col2'])
df['Col1'] = pd.Categorical(df.Col1)

pd.merge(df, df, on='Col1', how='inner')

Problem description

According to Pandas 0.23.0 doc :

for the keyword "how", the especially "inner" config (bolded part):

how : {‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘inner’

• left: use only keys from left frame, similar to a SQL left outer join; preserve key order
• right: use only keys from right frame, similar to a SQL right outer join; preserve key order
• outer: use union of keys from both frames, similar to a SQL full outer join; sort keys lexicographically
• inner: use intersection of keys from both frames, similar to a SQL inner join; preserve the order of the left keys

So the merge is supposed to preserve the order of the left keys.
However, if Categorical Data is used as the key to merge, the order is not preserved.

The output dataframe has been sorted as per the join key, and the index has been reseted.
Which obviously provoke some unwanted results.

Note that how='left' does provide the correct results (regarding key ordering).

Expected Output

Expected Output :

Actual Output :

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 2.7.14.final.0
python-bits: 64
OS: Linux
OS-release: 4.13.0-38-generic
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: None.None

pandas: 0.23.0
pytest: None
pip: 9.0.1
setuptools: 36.5.0.post20170921
Cython: None
numpy: 1.14.3
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 5.4.1
sphinx: None
patsy: None
dateutil: 2.6.1
pytz: 2018.4
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 0.9999999
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.9.6
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

@chrish42

[jschendel]

Thanks! I can confirm that this behavior is occurring on master. Note that this isn't an issue specific to Categorical as a join key, as the same behavior occurs if you don't do the conversion to Categorical:

In [2]: pd.__version__
Out[2]: '0.24.0.dev0+88.gdefdb34'

In [3]: df = pd.DataFrame([['A', 1],
   ...:                    ['B', 2],
   ...:                    ['B', 3],
   ...:                    ['A', 4]], columns=['Col1', 'Col2'])
   ...:

In [4]: pd.merge(df, df, on='Col1', how='inner')
Out[4]:
  Col1  Col2_x  Col2_y
0    A       1       1
1    A       1       4
2    A       4       1
3    A       4       4
4    B       2       2
5    B       2       3
6    B       3       2
7    B       3       3

As such, this appears to be a duplicate of #18776