BUG: Merging two data frames on a datetime index where one index is Object data type

[PCerles]

• [X ] I have checked that this issue has not already been reported.

• [ X] I have confirmed this bug exists on the latest version of pandas.

• [X ] (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

import numpy as np
import pandas as pd

df = pd.DataFrame(np.zeros((5, 5)))
df.columns = list('abcde')
df2 = pd.DataFrame(columns=list('abcfg'))

df['a'] = pd.Timestamp("2020-05-05")

df = df.set_index(['a', 'b', 'c'])
df2 = df2.set_index(['a', 'b', 'c'])

# error
df.merge(df2, on=['a'], how='left')

# no error
out = df.reset_index().merge(df2.reset_index(), how='left', on=['a'])

# no error
df.join(df2, how='left')

Problem description

We pull a lot of data from SQL into data frames. The current pandas.from_sql does not automatically set column data types, and they get reset anyway when we call a set_index. So when our getters return an empty result, none of the data types are set in the multi-index.

We have a lot of instances in our production codebase where we merge two dataframes in the above pattern. In pandas 0.25.3 (last used version), a DF missing values in a level of the datetime index was not a problem. However, in pandas 1.1.2, we get an error thrown whenever one of the levels in the multi-index is a datetime index, and one of the DFs in the join has a missing dtype.

Expected Output

Same as df.join(df2) or the other example shown.

Output of pd.show_versions()

commit : 2a7d332
python : 3.7.7.final.0
python-bits : 64
OS : Darwin
OS-release : 19.6.0
Version : Darwin Kernel Version 19.6.0: Thu Jun 18 20:49:00 PDT 2020; root:xnu-6153.141.1~1/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.1.2
numpy : 1.17.4
pytz : 2020.1
dateutil : 2.8.1
pip : 20.0.2
setuptools : 41.2.0
Cython : 0.29.21
pytest : 5.4.3
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.8.5 (dt dec pq3 ext lo64)
jinja2 : 2.10.3
IPython : 7.13.0
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : None
fastparquet : 0.4.1
gcsfs : None
matplotlib : 3.2.1
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 0.17.1
pytables : None
pyxlsb : None
s3fs : None
scipy : 1.5.2
sqlalchemy : 1.3.17
tables : None
tabulate : 0.8.7
xarray : None
xlrd : 1.2.0
xlwt : None

[dsaxton]

Worked at least in 0.23.5 so marking as a regression:

[ins] In [1]: import numpy as np
         ...: import pandas as pd
         ...:
         ...: df = pd.DataFrame(np.zeros((5, 5)))
         ...: df.columns = list('abcde')
         ...: df2 = pd.DataFrame(columns=list('abcfg'))
         ...:
         ...: df['a'] = pd.Timestamp("2020-05-05")
         ...:
         ...: df = df.set_index(['a', 'b', 'c'])
         ...: df2 = df2.set_index(['a', 'b', 'c'])
         ...:
         ...: # error
         ...: df.merge(df2, on=['a'], how='left')
         ...:
         ...:
Out[1]:
              d    e    f    g
a
2020-05-05  0.0  0.0  NaN  NaN
2020-05-05  0.0  0.0  NaN  NaN
2020-05-05  0.0  0.0  NaN  NaN
2020-05-05  0.0  0.0  NaN  NaN
2020-05-05  0.0  0.0  NaN  NaN

[ins] In [2]: pd.__version__
Out[2]: '0.25.3'