allow.cartesian should be more precise

[jangorecki]

Rather than having particular threshold, which is now nrow(x)+nrow(i) I propose to make row explosion exception (of course balancing backward compatibility) in either of those cases:

• a) there are duplicates in on columns in x
• b) there are duplicates in on columns in x AND i

Note that there is a related issue #2455

---

Case a)
Looks quite aggresive, but if we would just print message or cat to console then it shouldn't be big deal.
It gives a nice feature, that number of rows of result is never bigger than nrow(i), and in case of nomatch=NA it is always equal to nrow(i). So it corresponds to mult="error" feature (#1260). This feature is commonly expected when we join tables to lookup fields from one table to another.
Note that when we use update-on-join for that (join and :=) AFAIK it will automatically behaves like mult="last" rather than mult="all" (#3747, #2837), so row explosion is not hapenning. Also note that during update-on-join, x and i are swapped.

Case b) basically address cartesian product of duplicated entries.

I think it make sense to add those as options for now, so people can more easily evaluate them. Maybe allow.cartesian should handle case b), and new option handle a) ? or even just mult option: options("datatable.mult"="error").

d = function(n) as.data.table(list(x=rep(1L, n)))
d(1)[d(1), on="x"]
#   x
#1: 1
d(1)[d(2), on="x"]
#   x
#1: 1
#2: 1
d(2)[d(1), on="x"] # error in a) because we lookup from `x` and there are multiple matches!
#   x
#1: 1
#2: 1
d(2)[d(2), on="x"] # error in b) because there is cartesian product happening
#   x
#1: 1
#2: 1
#3: 1
#4: 1
d(1)[d(3), on="x"]
#   x
#1: 1
#2: 1
#3: 1
d(3)[d(1), on="x"] # error in a) duplicated entries in lookup table
#   x
#1: 1
#2: 1
#3: 1
d(3)[d(2), on="x"] # already errors
d(2)[d(3), on="x"]
d(3)[d(3), on="x"]
#Error in vecseq(f__, len__, if (allow.cartesian || notjoin || !anyDuplicated(f__,  : 
#  Join results in 9 rows; more than 6 = nrow(x)+nrow(i). Check for duplicate key values in i each of #which join to the same group in x over and over again. If that's ok, try by=.EACHI to run j for each #group to avoid the large allocation. If you are sure you wish to proceed, rerun with #allow.cartesian=TRUE. Otherwise, please search for this error message in the FAQ, Wiki, Stack #Overflow and data.table issue tracker for advice.

[jangorecki]

That would be breaking change, for example test 49.1 already assumes no error in such case:

# test 49.1
x = data.table(a=as.integer(c(1,3,4,4,4,4,7)), b=as.integer(c(5,5,6,6,9,9,2)), v=1:7)
setkey(x,a,b)
i = data.table(a=c(4,1,0,5,3,7,NA,4,1), b=c(6,5,1,10,5,2,1,6,NA))
x[i, v, nomatch=0L]
#[1] 3 4 1 2 7 3 4
x[,.N,c("a","b")][N>1L]
#   a b N
#1: 4 6 2
#2: 4 9 2
i[,.N,c("a","b")][N>1L]
#   a b N
#1: 4 6 2

# in group .(a=4L, b=6L) we have a cartesian product: 2x2 = 4 rows.

I think it make sense to provide that as alternative option, not a replacement of allow.cartesian

[jangorecki]

Proposed way to handle case a (using mult="error"), and case b (using new option to allow many-to-many join).

cc()
d = function(n) as.data.table(list(x=rep(1L, n)))

## case a: mult="error"

options("datatable.allow.cartesian"=TRUE, "datatable.join.many"=TRUE)
d(1)[d(1), on="x", mult="error"]
#   x
#1: 1
d(1)[d(2), on="x", mult="error"]
#   x
#1: 1
#2: 1
d(2)[d(1), on="x", mult="error"]
#Error in bmerge(i, x, leftcols, rightcols, roll, rollends, nomatch, mult,  : 
#  mult='error' and multiple matches during merge
d(2)[d(2), on="x", mult="error"]
#Error in bmerge(i, x, leftcols, rightcols, roll, rollends, nomatch, mult,  : 
#  mult='error' and multiple matches during merge
d(1)[d(3), on="x", mult="error"]
#   x
#1: 1
#2: 1
#3: 1
d(3)[d(1), on="x", mult="error"]
#Error in bmerge(i, x, leftcols, rightcols, roll, rollends, nomatch, mult,  : 
#  mult='error' and multiple matches during merge

## case b: options("datatable.join.many"=FALSE)

options("datatable.allow.cartesian"=TRUE, "datatable.join.many"=FALSE)
d(1)[d(1), on="x"]
#   x
#1: 1
d(1)[d(2), on="x"]
#   x
#1: 1
#2: 1
d(2)[d(1), on="x"]
#   x
#1: 1
#2: 1
d(2)[d(2), on="x"]
#Error in `[.data.table`(d(2), d(2), on = "x") : 
#  Joining resulted in many-to-many join. Perform quality check on your data, use mult!='all', or set 'datatable.join.many' option to TRUE to allow rows explosion.
d(1)[d(3), on="x"]
#   x
#1: 1
#2: 1
#3: 1
d(3)[d(1), on="x"]
#   x
#1: 1
#2: 1
#3: 1