Warn user about executing rolling join on differing column types

[ben519]

Got bit by this guy recently

dt <- data.table(ID=1:5, A=c(1.3, 1.7, 2.4, 0.9, 0.6))
buckets <- data.table(BucketID=1:4, BinA=1:4)

dt[, A.copy := A]

# Rolling join, int col with double col
buckets[dt, on=c("BinA"="A"), roll=-Inf]
   BucketID BinA ID A.copy
1:        1    1  1    1.3
2:        1    1  2    1.7
3:        2    2  3    2.4
4:        1    0  4    0.9
5:        1    0  5    0.6

# Rolling join, double col with double col (notice result is different than above)
buckets[, BinA := as.numeric(BinA)]
buckets[dt, on=c("BinA"="A"), roll=-Inf]
   BucketID BinA ID A.copy
1:        2  1.3  1    1.3
2:        2  1.7  2    1.7
3:        3  2.4  3    2.4
4:        1  0.9  4    0.9
5:        1  0.6  5    0.6

[franknarf1]

I was going to say that this (how BinA is displayed differently) can be handled by referring to cols with the x.* and i.* prefixes, but...

buckets[dt, on=c("BinA"="A"), roll=-Inf, .(x.BinA, i.A)]

   x.BinA i.A
1:      2 1.3
2:      2 1.7
3:      3 2.4
4:      1 0.9
5:      1 0.6

So when I ask for BinA from x, it just gives me the first col of x instead...? Seems like a bug.

[ben519]

Any chance this could get a [bug] label and added to the queue to be fixed? This issue scares me since it bites silently and subtly (and because I use a lot of rolling joins). Much thanks.

[jangorecki]

@franknarf1 it doesn't give you the first column but it gives you BinA after rolling. It looks like it gives BucketID because both columns have the same content.

buckets[dt, on=c("BinA"="A"), roll=-Inf, .(BinA, x.BinA)]
#    BinA x.BinA
#1:   1.3      2
#2:   1.7      2
#3:   2.4      3
#4:   0.9      1
#5:   0.6      1

@ben-schwen it must have been fixed, both queries return same results. Ultimately in non-equi joins prefixes .x and .i should be preferred till #1615 is resolved.