join column naming with `on` syntax

[eantonya]

I remember seeing a similar issue to this, but am failing to find it at the moment.

I find current naming of columns with on syntax to be confusing and counterintuitive. This becomes especially apparent when using non-equi joins. Right now the column name is the left side in an on = .(LHS = RHS) expression and imo it would make a lot more sense for it to be the right side.

X = data.table(pos = 1:3)
#   pos
#1:   1
#2:   2
#3:   3

Y = data.table(start = c(1, 3), end = c(2, 4))
#   start end
#1:     1   2
#2:     3   4

Y[X, on = .(start <= pos, end >= pos)]
#   start end
#1:     1   1
#2:     2   2
#3:     3   3

X[Y, on = .(pos >= start, pos <= end)]
#   pos pos.1
#1:   1     2
#2:   1     2
#3:   3     4

In both cases above it would make a lot more sense imo to have the column name taken from RHS.

[jangorecki]

Agree it makes more sense, but I'm not sure if we should change that default.
This issue overlaps with #1615.
Current behavior, the default when j is not provided, is consistent to base R merge (+ making name unique).

base::merge(X, Y, by.x=c("pos","pos"), by.y=c("start","end"))
#Empty data.table (0 rows) of 2 cols: pos,pos

The good thing is that it is just a matter of using j argument, and since it now handles both x. and i. prefixes, it should handle all cases.

[eantonya]

Sticking to a poor decision because base does it that way has never been the philosophy of data.table.

I agree that using x. and i. gives a workaround, but also quickly becomes a huge pain if you have many (non-join) columns (that you'll now have to either type out, or will have to change the names of the join columns later).

[jangorecki]

This is what the linked issue is about, to drop consistency to base R when joining with [.
I usually produce j as quoted expression in such cases. Not really user friendly but its quite straightforward and works like a charm :)

[eantonya]

Thanks - that was the issue I was looking for earlier.

[tdhock]

I completely agree with @eantonya -- I just ran into the same issue, and I dealt with it by using setnames after the join:

X = data.table(pos = 1:3)
Y = data.table(start = c(1, 3), end = c(2, 4))
joined.badColNames <- X[Y, on = .(pos >= start, pos <= end)]
joined.goodColNames <- data.table(joined.badColNames)
setnames(joined.goodColNames, c("start", "end"))

> joined.badColNames
   pos pos.1
1:   1     2
2:   1     2
3:   3     4
> joined.goodColNames
   start end
1:     1   2
2:     1   2
3:     3   4

it would be more user-friendly if data.table would rename the columns (and it would be backwards-compatible with the setnames code that I have already written)

[eantonya]

Another example: https://stackoverflow.com/a/47721442/817778

[ethanbsmith]

simple example of non-equi join column name masking

a <- data.table(foo = c(1:5))
b <- data.table(bar = c(1:5))

> a
   foo
1:   1
2:   2
3:   3
4:   4
5:   5
> b
   bar
1:   1
2:   2
3:   3
4:   4
5:   5

It's very odd that these outputs below are not identical. if you read all the related issues, many people expect them to be and keep getting surprised that they are not

identical(a[b, on = .(foo >= bar), .(foo, bar)], a[b, on = .(foo >= bar), .(x.foo, i.bar)])