I have noticed an inconsistency when using .SD in a non-equi join.
Is there an explanation for this?
Depending on the "direction" or "type" of join, using j = .SD throws an error.
library(data.table)
d1 <- fread("a, b
1, 11
6, 16")
d2 <- data.table(r = 1:5, s = seq(0, 20, 5))
d1
a b 1: 1 11 2: 6 16
d2
r s 1: 1 0 2: 2 5 3: 3 10 4: 4 15 5: 5 20
d1[d2, on = .(a <= s, b >= s)]
a b r 1: 0 0 1 2: 5 5 2 3: 10 10 3 4: 10 10 3 5: 15 15 4 6: 20 20 5
d1[d2, on = .(a <= s, b >= s), j = .SD]
Error in
[.data.table(d1, d2, on = .(a <= s, b >= s), j = .SD) :
column(s) not found: a
d2[d1, on = .(s >= a, s <= b)]
r s s.1 1: 2 1 11 2: 3 1 11 3: 3 6 16 4: 4 6 16
d2[d1, on = .(s >= a, s <= b), j = .SD]
r s 1: 2 1 2: 3 1 3: 3 6 4: 4 6
I have reproduced the behaviour with R version 3.6.0 and data.table versions 1.11.8, 1.12.2, and 1.12.3 (development version on github).
I am aware that there are related discussions on github, e.g., Both columns for rolling and non-equi joins #3093, .SD in expression with j? #3115 but I haven't found (perhaps overlooked?) an explanation for the observed behaviour there.