Repository navigation
non-equi join masks column names from x and i in the j expression #2595
Description
Activity
may be related to #2569
To me this is very much related to the general weird behaviour of non-equi joins. The column that is joined on looks very weird after the join. Just doing:
a <- data.table(foo = c(1:5)) b <- data.table(bar = c(1:5), iRows = 1:5) a[b, on = .(foo >= bar)]yields
# foo iRows # 1: 1 1 # 2: 1 1 # 3: 1 1 # 4: 1 1 # 5: 1 1 # 6: 2 2 # 7: 2 2 # 8: 2 2 # 9: 2 2 # 10: 3 3 # 11: 3 3 # 12: 3 3 # 13: 4 4 # 14: 4 4 # 15: 5 5Here you see, that grouping by iRows (which is essentially, what by = .EACHi does) is naturally giving the unexpected result that you saw.
It is just weird that the resultingfoocolumn is so different from the originalx.foocolumn. Essentially foo is not x.foo, but i.bar. This seems close to a bug to me and I don't know, why this behaviour was chosen. I would expect the following:a[b, on = .(foo >= bar)] # foo iRows # 1: 1 1 # 2: 2 1 # 3: 3 1 # 4: 4 1 # 5: 5 1 # 6: 2 2 # 7: 3 2 # 8: 4 2 # 9: 5 2 # 10: 3 3 # 11: 4 3 # 12: 5 3 # 13: 4 4 # 14: 5 4 # 15: 5 5MAybe @mattdowle or @arunsrinivasan can comment on this.
@MarkusBonsch this behaviour is discussed in #1615. We can eventually force users to use
x.andi.prefixes to avoid confusion.@ethanbsmith I am closing this issue because the "bug" can be explained by known data.table behaviour. If you disagree, don't hesitate to reopen.
this issue just bit me again and cost me hours to track down. I think this is a real bug that should be addressed, but I will defer to the owners. just my $.02 below:
- i think the better question to ask is is this desired/reasonable behavior, vs. is it known behavior
- to quote @MarkusBonsch: Essentially
foois notx.foo, buti.bar.This seems close to a bug to me and I don't know, why this behaviour was chosen. Thatfoomasksx.fooin name resolution is the heart of the problem here. - to quote @arunsrinivasan in this SO Issuer: For some reason, using
frameinstead ofx.frame, i.e.,frame[which.max(signal)], returns allNA,which I'd suppose is a bug .. Could you please file an issue by linking to this post? Thanks. - when using non-equi joins you are pretty much required to uses the x. and i. prefixes. this is not the case for equi-joins. i would assume that most people would prefer consitency.
- this really bites when prototyping and moving between equi/non-equi joins. a simple
>or<can entirely break your code - I don't think that join in
[.data.tablecould be consistent to SQL #1615 really explains this issue. join in[.data.tablecould be consistent to SQL #1615 adds support for x. and i. prefixes, but does not discuss that the are required.
- changed the title
[-]non-equi join with .EACHI produces invalid results without x. prefix[/-][+]non-equi join masks column names from x and i in the j expression[/+]on Mar 5, 2018 simpler example highlighting the masking problem:
a <- data.table(foo = c(1:5)) b <- data.table(bar = c(1:5)) identical(a[b, on = .(foo >= bar), .(foo, bar)], a[b, on = .(foo >= bar), .(x.foo, i.bar)])@ethanbsmith ad.2 please read why this behaviour was "chosen" in #1615, ad.4 I agree, ad.5 read comments there too, prefixes are required as of now, they are just not enforced on user while they probably should be.
Please discuss this behaviour in single issue, best the place where it was already discussed. You can also include some minimal test cases there so we can include them when closing #1615. Easiest way seems to be warning on lack of prefixes when column names mismatch occurs.
- added this to the milestone
on Apr 10, 2018
This was very hard to track down. If prefixes are indeed required in this scenario, maybe a warning or error could be issued
might be related to #2313, #1761
This version produces incorrect values for
fmeanandfmaxThis version, with the prefix works as expected: