Behaviour difference of data.table merge(x,y, incomparables = NA) and dplyr

Start by reproducing the supplied merge.data.table examples using incomparables = NA, then inspect merge.data.table's handling of all.x and NA keys. Compare the result with the dplyr left_join(..., na_matches = 'never') example; done means the expected behavior and the discrepancy are clarified and appropriately covered or explained.

First of all, thank you for implementing the incomparables feature in the development release of {data.table}. I am not sure if my following query is related to a bug or perhaps just a difference in design between base R, data.table and dplyr joins on columns with NA. Was hoping for someone to clarify the expected behaviour.

With the latest updates to {data.table}, previous issues flagging the use of NA matches in joins was addressed. This works for both single and multiple columns with NAs during a join, unlike merge.data.frame, which will fail.

However, I noticed that there is a behaviour difference if compared to a similar operation in {dplyr}: *_join(x,y, na_matches = 'never'). Whereas {dplyr} will not drop rows during a left join from the LHS table, it appears the data.table merged (merge(x,y,incomparables = NA)) will drop those rows. Please see the REPREXs below for more detail.

Base R

Throws error when merge on multiple columns with NA. Previously known, as shown at this source: https://community.rstudio.com/t/why-does-na-match-na-when-joining-two-dataframes/28785/3

(d1 <- data.frame(x1 = c(1, NA, 3), x2 = c(TRUE, NA, FALSE), y = c("a", "b", "c")))
#>   x1    x2 y
#> 1  1  TRUE a
#> 2 NA    NA b
#> 3  3 FALSE c

(d2 <- data.frame(x1 = c(1, NA, NA), x2 = c(TRUE, NA, FALSE), z = c("A", "B", "C")))
#>   x1    x2 z
#> 1  1  TRUE A
#> 2 NA    NA B
#> 3 NA FALSE C

merge.data.frame(d1, d2, all.x = TRUE, all.y = FALSE, incomparables = NA)
#> Error in merge.data.frame(d1, d2, all.x = TRUE, all.y = FALSE, incomparables = NA): 'incomparables' is supported only for merging on a single column

merge.data.frame(d1, d2, all.x = TRUE, all.y = FALSE)
#>   x1    x2 y    z
#> 1  1  TRUE a    A
#> 2  3 FALSE c <NA>
#> 3 NA    NA b    B

^{Created on 2023-05-03 with reprex v2.0.2}

Data.table

data.table 1.14.9 IN DEVELOPMENT built 2023-02-17 18:32:02 UTC;

Unlike base R dataframes, the data.table version has no issues with multiple columns, but it drops all rows in a left join that had NA.

library(data.table)

(dt1 <- data.table(x1 = c(1, NA, 3), x2 = c(TRUE, NA, FALSE), y = c("a", "b", "c")))
#>       x1     x2      y
#>    <num> <lgcl> <char>
#> 1:     1   TRUE      a
#> 2:    NA     NA      b
#> 3:     3  FALSE      c

(dt2 <- data.table(x1 = c(1, NA, NA), x2 = c(TRUE, NA, FALSE), z = c("A", "B", "C")))
#>       x1     x2      z
#>    <num> <lgcl> <char>
#> 1:     1   TRUE      A
#> 2:    NA     NA      B
#> 3:    NA  FALSE      C

merge.data.table(dt1, dt2, all.x = TRUE, all.y = FALSE, incomparables = NA)
#> Key: <x1, x2>
#>       x1     x2      y      z
#>    <num> <lgcl> <char> <char>
#> 1:     1   TRUE      a      A
#> 2:     3  FALSE      c   <NA>

merge.data.table(dt1, dt2, all.x = TRUE, all.y = FALSE)
#> Key: <x1, x2>
#>       x1     x2      y      z
#>    <num> <lgcl> <char> <char>
#> 1:    NA     NA      b      B
#> 2:     1   TRUE      a      A
#> 3:     3  FALSE      c   <NA>

^{Created on 2023-05-03 with reprex v2.0.2}

Dplyr

Using the left_join() function, the na_matches parameter keep all rows from the left table, and does not drop the rows with NA. When not matching on NA, the columns added from right-hand table are NA.

library(dplyr)

d1 <- data.frame(x1 = c(1, NA, 3), x2 = c(TRUE, NA, FALSE), y = c("a", "b", "c"))
d2 <- data.frame(x1 = c(1, NA, NA), x2 = c(TRUE, NA, FALSE), z = c("A", "B", "C"))

left_join(d1, d2, na_matches = 'never')
#> Joining with `by = join_by(x1, x2)`
#>   x1    x2 y    z
#> 1  1  TRUE a    A
#> 2 NA    NA b <NA>
#> 3  3 FALSE c <NA>

left_join(d1, d2)
#> Joining with `by = join_by(x1, x2)`
#>   x1    x2 y    z
#> 1  1  TRUE a    A
#> 2 NA    NA b    B
#> 3  3 FALSE c <NA>

^{Created on 2023-05-03 with reprex v2.0.2}

Session Information

sessionInfo()

R version 3.6.3 (2020-02-29)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: RHEL

Matrix products: default
BLAS/LAPACK: /usr/lib64/libopenblasp-r0.3.3.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8   
 [6] LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices datasets  utils     methods   base     

other attached packages:
[1] data.table_1.14.9

loaded via a namespace (and not attached):
 [1] fansi_1.0.4      utf8_1.2.3       dplyr_1.1.2      R6_2.5.1         lifecycle_1.0.3  magrittr_2.0.3   pillar_1.9.0     rlang_1.1.1      cli_3.6.1       
[10] renv_0.15.5      vctrs_0.6.2      generics_0.1.3   tools_3.6.3      glue_1.6.2       compiler_3.6.3   pkgconfig_2.0.3  tidyselect_1.2.0 tibble_3.2.1