Use `==` in `closest` join

[maellecoursonnais]

closest only accepts inequality operators, but it would seem natural to have == (or some other operator) for joining with the closest value, irrespective of whether it is higher or lower.

---

Consider this minimal example (data taken from this SO question). This issue was also mentioned here. Inequality join with closest is okay, but equality join with closest are not working.

library(lubridate)
library(dplyr)
df1 <- data.frame(var2 = c("Dog", "Dog", "Cat"),
                  Date = dmy(c("01-01-2022","02-01-2022" , "07-12-2022")))
#   var2       Date
# 1  Dog 2022-01-01
# 2  Dog 2022-01-02
# 3  Cat 2022-12-07

df2 <- data.frame(Date = dmy(c("07-01-2022","04-12-2022" , "10-12-2022")))
#         Date
# 1 2022-01-07
# 2 2022-12-04
# 3 2022-12-10

df1 %>% 
  inner_join(df2, join_by(closest(Date <= Date)))
#    var2    Date.x     Date.y
# 1  Dog 2022-01-01 2022-01-07
# 2  Dog 2022-01-02 2022-01-07
# 3  Cat 2022-12-07 2022-12-10

df1 %>% 
  inner_join(df2, join_by(closest(Date == Date)))

# Error in `join_by()`:
#   ! The expression used in `closest()` can't use `==`.
# ℹ Expression 1 is `closest(Date == Date)`.
# Run `rlang::last_error()` to see where the error occurred.

Instead, it'd be nice to have a simple option for either direction:

df1 %>% 
  inner_join(df2, join_by(closest(Date == Date)), multiple = "all")
#    var2    Date.x     Date.y
# 1  Dog 2022-01-01 2022-01-07
# 2  Dog 2022-01-02 2022-01-07
# 3  Cat 2022-12-07 2022-12-04
# 4  Cat 2022-12-07 2022-12-10

[DavisVaughan]

I recognize that in a few places this could be useful, but when developing these joins the tradeoff of trying to add support for this was overwhelmingly not worth the amount of work required.

The problem is mainly that closest(x <= y) can work based off relative ordering, which means it works for any types that can be trivially ordered (even things like "a" < "b" work).

The idea that you are getting at with closest(x == y) would be much harder, because you'd need to actually be able to compute absolute distances to figure out which side to use (i.e. 3 is closer to 4 than 7 is, but you need to compute the absolute distance from 4 to know that). This restricts the number of types it works on greatly, i.e. "b" - "a" would have to work but is basically meaningless. It also means that we'd have to define some kind of R callback to compute the difference repeatedly, which was a nonstarter performance-wise (this was basically what is done in one of those SO answers with abs(Date.x - Date.y), an R call).

---

I also don't think closest(x == y) is the right language for this.

closest(x <= y) says "for each element of x, find all matches where x[i] <= y then filter down to the value of y that is closest to x[i]"

closest(x == y) can't be interpreted the same way (i.e. you can't frame it like a filter).

---

I realize that data.table has "nearest" in their API, but it is heavily restricted to just numeric storage types.

When I was working on the API, it seemed like the overwhelming majority of the use cases really didn't want that, they wanted a directional rolling join like <= or >=, because that direction typically matters for most business use cases.

---

Lastly, some SQL data bases support ASOF joins, and I think duckdb is also planning on adding them, but I don't think any of these support the "nearest" idea, so we probably couldn't ever have a native sql translation for it.

[tdlong]

I am also very interested in this thread. There are many cases in genomics where we really want closest to mean closest (as opposed to closest to the left or right). Since after the closest join (using <) both values used in the join are returned, it would seem possible to take the resulting df and do a second join (using >) and then having some sort of wrapper that decides which join was truly the closest and sorting through the columns that tagged along.