Merge and [ should have an argument for avoid matching of NA

[juancentro]

Actually, NA matches NA in both merge and [. To emulate SQL behavior, there should be an argument to avoid NA matching with anything

d = data.table
d(a=c(1:2,NA,NA))[d(a=c(2:3,NA,NA)), on="a"]
# should return only 2:3

[MichaelChirico]

Got bit by the following:

DT1 = data.table(ID = c(1:1000, rep(NA, 9000)))
DT2 = data.table(ID = c(1:100000, rep(NA, 900000)), v = rnorm(1e6))
DT1[DT2, on = 'ID', v := i.v]

Error in vecseq(f__, len__, if (allow.cartesian || notjoin || !anyDuplicated(f__, :

Join results in more than 2^31 rows (internal vecseq reached physical limit). Very likely misspecified join. 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. Otherwise, please search for this error message in the FAQ, Wiki, Stack Overflow and data.table issue tracker for advice.

The default to merge NA to NA blew this problem up; actually I'm just trying to merge on = 'ID' conditional on !is.na(ID). The syntax to do so is substantially more awkward/unintuitive (though this would be much improved by #2911):

DT1[!is.na(ID), v := DT2[.SD, on = 'ID', x.v]]

Honestly I think the default should be not to match NA and NA.

I get some weird error on the base version:

setDF(DT1); setDF(DT2)
merge(DT1, DT2, by = 'ID', all = TRUE)

Error in merge.data.frame(DT1, DT2, by = "ID", all = TRUE) :
negative length vectors are not allowed

But this scaled down version says base matches NA to NA:

DT1 = data.frame(ID = NA)
DT2 = data.frame(ID = NA)
merge(DT1, DT2, by = 'ID', all = TRUE)
#   ID
# 1 NA

This is weird because is.na(NA == NA), whereas a merge suggests finding x.values == i.values.

[jangorecki]

negative length vectors are not allowed

maybe integer overflow.

[MichaelChirico]

yes, thanks @sritchie73:

https://stackoverflow.com/a/48676389/3576984

[MichaelChirico]

Unfortunately I think we should match the {base} merge() behavior by default, even though I disagree with it:

merge(data.frame(a=NA, b=1), data.frame(a=NA, c=2:3), by='a', all=TRUE)
#    a b c
# 1 NA 1 2
# 2 NA 1 3

Still it would be nice to put the option to consider NA incomparable:

merge(data.frame(a=NA, b=1), data.frame(a=NA, c=2:3), by='a', all=TRUE, incomparables=NA)
#    a  b  c
# 1 NA  1 NA
# 2 NA NA  2
# 3 NA NA  3

Whereas we don't support that:

merge(data.table(a=NA, b=1), data.table(a=NA, c=2:3), by='a', all=TRUE, incomparables=NA)
# Empty data.table (0 rows and 3 cols): a,b,c

[jangorecki]

Something possibly to add to mergelist which should be less complicated than [.