BUG: DataFrame.merge on Timestamp column broken if when timestamps are timezone-aware and have different units

[georgipeev]

Pandas version checks

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

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

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
import pytz
df1 = pd.DataFrame({'t': [pd.Timestamp(2023, 5, 12, tzinfo=pytz.timezone('America/Chicago'))], 'a': [1]})
df2 = df1.copy()
df2['t'] = df2['t'].dt.as_unit('s')
df1.merge(df2, on='t')
# Returns Empty DataFrame

Issue Description

In Pandas 2 DataFrame.merge seems to consider Timestamps as different if they are timezone-aware and have different units, even when their timezone and date/time values are the same, and they compare as equal via ==. The behavior of DataFrame.merge is correct for timezone-unaware Timestamps even when they have different units. More details shown in code below:

>>> import pandas as pd
>>> import pytz
>>> df1 = pd.DataFrame({'t': [pd.Timestamp(2023, 5, 12, tzinfo=pytz.timezone('America/Chicago'))], 'a': [1]})
>>> df2 = df1.copy()
>>> df2['t'] = df2['t'].dt.as_unit('s')
>>> df1
                          t  a
0 2023-05-12 00:00:00-05:00  1
>>> df2
                          t  a
0 2023-05-12 00:00:00-05:00  1
>>> df1.dtypes
t    datetime64[ns, America/Chicago]
a                              int64
dtype: object
>>> df2.dtypes
t    datetime64[s, America/Chicago]
a                             int64
dtype: object
>>> df1['t'] == df2['t']
0    True
Name: t, dtype: bool
>>> df1.merge(df2, on='t')               # Incorrect behavior
Empty DataFrame
Columns: [t, a_x, a_y]
Index: []
>>> df1.merge(df2, on='t', how='left')   # Incorrect behavior
                           t  a_x  a_y
0  2023-05-12 00:00:00-05:00    1  NaN

Expected Behavior

>>> import pandas as pd
>>> import pytz
>>> df1 = pd.DataFrame({'t': [pd.Timestamp(2023, 5, 12, tzinfo=pytz.timezone('America/Chicago'))], 'a': [1]})
>>> df2 = df1.copy()
>>> df2['t'] = df2['t'].dt.as_unit('s')
>>> df1.merge(df2, on='t')
                          t  a_x  a_y
0 2023-05-12 00:00:00-05:00    1    1

Installed Versions

INSTALLED VERSIONS

commit : 37ea63d
python : 3.9.16.final.0
python-bits : 64
OS : Linux
OS-release : 4.18.0-348.23.1.el8_5.jump2.x86_64
Version : #1 SMP Tue Nov 1 14:32:55 CDT 2022
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.0.1
numpy : 1.22.4
pytz : 2022.2.1
dateutil : 2.8.2
setuptools : 66.0.0
pip : 23.1.2
Cython : 0.29.32
pytest : 7.3.1
hypothesis : None
sphinx : 7.0.0
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.9.1
html5lib : 1.1
pymysql : 1.0.2
psycopg2 : None
jinja2 : 3.1.2
IPython : None
pandas_datareader: None
bs4 : 4.11.2
bottleneck : None
brotli : None
fastparquet : None
fsspec : 2023.5.0
gcsfs : None
matplotlib : None
numba : 0.56.0
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 12.0.0
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.10.1
snappy : None
sqlalchemy : 1.4.40
tables : None
tabulate : 0.8.10
xarray : None
xlrd : None
zstandard : 0.21.0
tzdata : 2022.2
qtpy : None
pyqt5 : None

[jorisvandenbossche]

@georgipeev thanks for the report! That's indeed buggy, also based on a TODO we still needed to fix different resolutions in merge ..

I am going to label this for 2.0.2 / as a regression, because while you couldn't have different resolutions before (so it's a bug in a new feature), you can actually end up with different resolutions without changing your code nowadays, and that way it could still be a regression in your code.