BUG: merge should upcast to highest resolution

[lithomas1]

• closes BUG: df1.merge(df2, on='ds', how='outer') not convert column ds to the highest resolution datetime type of the two dfs #55212 (Replace xxxx with the GitHub issue number)
• Tests added and passed if fixing a bug or adding a new feature
• All code checks passed.
• Added type annotations to new arguments/methods/functions.
• Added an entry in the latest doc/source/whatsnew/vX.X.X.rst file if fixing a bug or adding a new feature.

[lithomas1]

@jbrockmendel

This only gets some of the cases.

I think this misses when we take everything from the left join col.
Any idea where to handle the upcasting then?

[jbrockmendel]

pass copy=False?

[jbrockmendel]

I think this misses when we take everything from the left join col. Any idea where to handle the upcasting then?

Do you have an example? That'd save me some time in figuring out what code paths that case goes through

[lithomas1]

Sorry for the slow reply.

I think my changes here only fixed the result for outer/right merges. The tests that I've added that are failing are doing so for inner/left merges.

Here's a MRE.

t1 = '2023-09-01'
t2 = '2023-09-01 01:00:00'
t3 = '2023-09-01 01:30:00'
ds1 = pd.date_range(t1, t2, freq='30T')
ds2 = pd.date_range(t1, t3, freq='30T')
df1 = pd.DataFrame({
    'ds': ds1.astype('datetime64[us]'),
    'y1': range(len(ds1)),
})
df2 = pd.DataFrame({
    'ds': ds2.astype('datetime64[ns]'),
    'y2': range(len(ds2)),
})
# Changing this to outer/right makes it work
df3 = df1.merge(df2, on=['ds'], how='left') #will only convert df2 `ds` type to df1 `ds` type
df3.dtypes

[jbrockmendel]

in main they are able to join just gives the wrong result reso?

[jbrockmendel]

The code change looks good to me. The test change looks plausible but i haven't examined closely.

[github-actions]

This pull request is stale because it has been open for thirty days with no activity. Please update and respond to this comment if you're still interested in working on this.

[mroeschke]

Looks like this PR has gone stale and needs a rebase. Closing to clear the queue but feel free to reopen when you have time to circle back