bug: asof_join on different column names gives wrong result

[mesejo]

What happened?

The following code snippet produces the wrong output:

import ibis

con = ibis.duckdb.connect()

left = con.create_table("left2", {
    "id": [1, 2],
    "statement_date": ["2024-01-31", "2024-02-28"],
}).mutate(statement_date=ibis._.statement_date.cast("timestamp"))

right = con.create_table("right2", {
    "id": [1, 2],
    "transaction_date": ["2024-01-26", "2024-02-08"],  # Same dates as Case 1!
    "info": ["X", "Y"],
}).mutate(transaction_date=ibis._.transaction_date.cast("timestamp"))

expr = left.asof_join(
    right,
    on=left.statement_date >= right.transaction_date,
    predicates=[left.id == right.id],
    tolerance=ibis.interval(days=10),
)

print(expr.execute())

Output

   id statement_date id_right transaction_date  info
0   2     2024-02-28     None              NaT  None
1   1     2024-01-31     None              NaT  None

The proper output should be:

   id statement_date  id_right transaction_date  info
0   1     2024-01-31       1.0       2024-01-26     X
1   2     2024-02-28       NaN              NaT  None

What version of ibis are you using?

11.0.0

What backend(s) are you using, if any?

DuckDB

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct