cannot join on column with datetime when using the 'on' parameter

[filthysocks]

Code Sample, a copy-pastable example if possible

import pandas as pd

left = pd.DataFrame({'date':['2019-01-01'], 'a': [1]})
left['date'] = pd.to_datetime(left['date'], utc=True)

right = pd.DataFrame({'date':['2019-01-01'], 'b': [1]})
right['date'] = pd.to_datetime(right['date'], utc=True)

# execption
left.join(right, on='date')

Problem description

ValueError: You are trying to merge on datetime64[ns, UTC] and int64 columns. If you wish to proceed you should use pd.concat

Both column have the same dtype when calling join, somehow the right join colum gets converted to int64. This causes the join to fail.
However, the follwing code works fine:

# init as above
left = left.set_index('date')
right = right.set_index('date')
left.join(right)
"""
                           a  b
date                           
2019-01-01 00:00:00+00:00  1  1

"""

Expected Output

date                       a  b                           
2019-01-01 00:00:00+00:00  1  1

Output of pd.show_versions()

commit: None python: 3.7.3.final.0 python-bits: 64 OS: Linux OS-release: 4.15.0-54-generic machine: x86_64 processor: x86_64 byteorder: little LC_ALL: None LANG: de_DE.UTF-8 LOCALE: de_DE.UTF-8

pandas: 0.24.2
pytest: 4.3.1
pip: 10.0.1
setuptools: 39.1.0
Cython: None
numpy: 1.16.4
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2019.1
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10.1
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[mroeschke]

Thanks for the report; can replicate on master. Investigations and PR's welcome!

[mroeschke]

Actually this is the expected behavior as documented:

on : str, list of str, or array-like, optional
Column or index level name(s) in the caller to join on the index in other

For your use case, you probably want to use merge instead

[filthysocks]

oh well i see. thanks.