Weird interaction of join and .EACHI

[OfekShilon]

DT1 <- data.table(product = c("P1","P2","P3"), foo = 1:6)
DT2 <- data.table(product = c("P1","P2","P3"), family = c("A","A","B"))
DT1[DT2,.(product, family), on = "product", by = .EACHI]

gives:

   product product family
1:      P1      P1      A
2:      P2      P1      A
3:      P3      P1      B

It seems like the 2nd 'product' column should never have been there, and its contents are bogus.
Moreover, removing foo from DT1 changes it:

> DT1 <- data.table(product = c("P1","P2","P3"))
> DT2 <- data.table(product = c("P1","P2","P3"), family = c("A","A","B"))
> DT1[DT2,.(product, family), on = "product", by = .EACHI]
   product product family
1:      P1      P1      A
2:      P2      P2      A
3:      P3      P3      B

# Output of sessionInfo()
R version 3.4.0 (--)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 18.04.5 LTS

Matrix products: default
BLAS: /opt/R-3.4.0.mkl/lib64/R/lib/libR.so
LAPACK: /opt/R-3.4.0.mkl/lib64/R/lib/libRlapack.so

locale:
[1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C LC_TIME=C LC_COLLATE=C LC_MONETARY=C LC_MESSAGES=C LC_PAPER=C LC_NAME=C LC_ADDRESS=C
[10] LC_TELEPHONE=C LC_MEASUREMENT=C LC_IDENTIFICATION=C

attached base packages:
[1] stats graphics grDevices utils datasets methods base

other attached packages:
[1] yaml_2.2.0 R.utils_2.5.0 R.oo_1.21.0 R.methodsS3_1.7.1 xts_0.9-7 zoo_1.8-0 bit64_4.0.5 bit_4.0.4 data.table_1.14.1

loaded via a namespace (and not attached):
[1] compiler_3.4.0 tools_3.4.0 grid_3.4.0 lattice_0.20-35

[Henrik-P]

FWIW, the difference between your first and second "DT1" is not "only" the lack of the "foo" variable in the latter, it also has a different "product" variable. It may seem that "foo" or not isn't the direct cause of the of the two different join outcomes, rather the order of the join variable in x, "product".

Below, DT1_1 has the same "product" column as your first DT1 (not ordered), but no "foo" column:

DT1_1 = data.table(product = rep(c("P1","P2","P3"), 2))
DT1_1
#    product
# 1:      P1
# 2:      P2
# 3:      P3
# 4:      P1
# 5:      P2
# 6:      P3

Joining it with "DT2" reproduces your first result:

DT1_1[DT2, .(product, family), on = "product", by = .EACHI]
#    product product family
# 1:      P1      P1      A
# 2:      P2      P1      A
# 3:      P3      P1      B

On the other hand, if the join column in x has the same set of values as in "DT1_1" but ordered, the result is the same as in your second example:

DT1_2 = data.table(product = rep(c("P1","P2","P3"), each = 2))
DT1_2
#    product
# 1:      P1
# 2:      P1
# 3:      P2
# 4:      P2
# 5:      P3
# 6:      P3

DT1_2[DT2, .(product, family), on = "product", by = .EACHI]
#    product product family
# 1:      P1      P1      A
# 2:      P2      P2      A
# 3:      P3      P3      B

[duy-n]

Referring to the joined column within j= while using by=.EACHI doesn't do what you'd expect:

library(data.table)
packageVersion("data.table")
#> [1] '1.18.2.1'

dt1 <- data.table(a=c(9,2,3,2), b=1:4)
dt2 <- data.table(a=c(3,2,7), b=21:23)

# inner join
dt1[dt2, on="a", by=.EACHI, nomatch=NULL, .(
  aa=list(a), x.a=list(x.a), i.a=list(i.a), bb=list(b), x.b=list(x.b), i.b=list(i.b)
)]
#>        a     aa    x.a    i.a     bb    x.b    i.b
#>    <num> <list> <list> <list> <list> <list> <list>
#> 1:     3      9      3      2      3      3     22
#> 2:     2      9    2,2      2    2,4    2,4     22

# right join
dt1[dt2, on="a", by=.EACHI, .(
  aa=list(a), x.a=list(x.a) , i.a=list(i.a), bb=list(b), x.b=list(x.b), i.b=list(i.b)
)]
#>        a     aa    x.a    i.a     bb    x.b    i.b
#>    <num> <list> <list> <list> <list> <list> <list>
#> 1:     3     NA      3      7      3      3     23
#> 2:     2     NA    2,2      7    2,4    2,4     23
#> 3:     7     NA     NA      7     NA     NA     23

^{Created on 2026-03-04 with reprex v2.1.1}

A few weird things in the output, from a user perspective:

1. You'd expect aa == x.a just like bb == x.b. Instead, aa seems to give the first value of the joined column in x[] if there aren't any unmatched rows in [i], and NA if there are, regardless of the group being processed.
2. i.a and i.b seem to give the last values in the joined dataset, also ignoring the current group.

Both of these look like bugs; they're not documented to work that way (from what I could read).