BUG: merge_asof does not work with datetime by column on Pandas 2.1.x

[rg9066]

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

df1 = pd.DataFrame(
    [
        ['2023-01-01', '2023-01-01'], 
        ['2023-01-02', '2023-01-01'],
        ['2023-01-03', '2023-01-02'],
    ], 
    columns=['date_1', 'date_2']
)
df1['date_1'] = pd.to_datetime(df1['date_1'])
df1['date_2'] = pd.to_datetime(df1['date_2'])

df2 = pd.DataFrame(
    [
        ['2023-02-01', '2023-01-01'], 
    ], 
    columns=['date_1', 'date_2']
)
df2['date_1'] = pd.to_datetime(df2['date_1'])
df2['date_2'] = pd.to_datetime(df2['date_2'])

df = pd.merge_asof(df1, df2, on='date_1', by='date_2')
print(df)

Issue Description

I get the following error when trying to do merge_asof with the 'by' column being a datetime64[ns] dtype. I've verified it doesn't work with other datetime units (e.g. datetime64[s] dtype), or with by=None.

Traceback (most recent call last):
    df = pd.merge_asof(df1, df2, on='date_1', by='date_2')
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/miniconda3/envs/analytics/lib/python3.11/site-packages/pandas/core/reshape/merge.py", line 705, in merge_asof
    return op.get_result()
           ^^^^^^^^^^^^^^^
  File "/home/miniconda3/envs/analytics/lib/python3.11/site-packages/pandas/core/reshape/merge.py", line 1852, in get_result
    join_index, left_indexer, right_indexer = self._get_join_info()
                                              ^^^^^^^^^^^^^^^^^^^^^
  File "/home/miniconda3/envs/analytics/lib/python3.11/site-packages/pandas/core/reshape/merge.py", line 1133, in _get_join_info
    (left_indexer, right_indexer) = self._get_join_indexers()
                                    ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/miniconda3/envs/analytics/lib/python3.11/site-packages/pandas/core/reshape/merge.py", line 2217, in _get_join_indexers
    return func(
           ^^^^^
  File "join.pyx", line 682, in pandas._libs.join.__pyx_fused_cpdef
TypeError: Function call with ambiguous argument types

Temporary workaround is to convert the 'by' column to int64 first, perform the merge_asof, then convert the 'by' column back to datetime64[ns]:

import numpy as np

df1['date_2'] = df1['date_2'].values.astype(np.int64)
df2['date_2'] = df2['date_2'].values.astype(np.int64)

df = pd.merge_asof(df1, df2, on='date_1', by='date_2')
    
df['date_2'] = pd.to_datetime(df['date_2'])

### Expected Behavior

Expect merging with a datetime64 'by' column to be acceptable without having to first convert to int64. This used to work in pandas 1.5.x

### Installed Versions

<details>
python : 3.11.5.final.0
OS : Linux
pandas : 2.1.0
</details>

[jorisvandenbossche]

@rg9066 thanks for the report! I can confirm the regression compared to 2.0, and it also still fails on the main branch.

From looking at the related code changes in where we call the cython function in _AsOfMerge._get_join_indexers, this might be caused by #53771

cc @jbrockmendel

[jbrockmendel]

#53771 is plausible. IIUC reverting that would cause this case to cast to object, which we should try to avoid. Hopefully casting to int64 will get the job done instead. something like #54975 would be ideal long-term.