Database semi_join() doesn't match R's NA semantics

[mkirzon]

I was attempting a dplyr semi-join against SQL Server tables. In regular dplyr with local dataframes, semi-joins can succesfully join on NULL values between the left and right table. However, this is not the default behavior in SQL Server. For example, dbplyr implements semi-joins with the WHERE EXISTS method, where the WHERE clause will specify the join conditions. By default, dbplyr generates the following SQL code:

SELECT *   
FROM dbo.Test AS "TBL_LEFT"
WHERE EXISTS (
  SELECT 1
  FROM (
    ... -- skipping code here
  ) "TBL_RIGHT"
  WHERE (
    ("TBL_LEFT"."id" = "TBL_RIGHT"."id")
  )
)

To make the semi-join on SQL Server allow joins on NULLs, we'd modify the final WHERE clause to:

WHERE (
    ("TBL_LEFT"."id" = "TBL_RIGHT"."id" OR 
    ("TBL_LEFT"."id" IS NULL AND "TBL_RIGHT"."id" IS NULL))
)

I so far don't see a way that we'd be able to create the 2nd version from dplyr. Perhaps, we need a new parameter in the semi_join()

[hadley]

Or use one of the alternatives discussed in https://modern-sql.com/feature/is-distinct-from

• SQLite: IS
• MySQL: <=>
• PostgresSQL: IS DISTINCT FROM
• Oracle: DECODE()
• MS SQL (and all others): (CASE WHEN (a = b) or (a IS NULL AND b IS NULL) THEN 0 ELSE 1) = 0

[hadley]

i.e. we need to use the na_matches argument

[hadley]

Reprex:

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

mf1 <- memdb_frame(x = c(1, NA, 3))
mf2 <- memdb_frame(x = c(1, NA))

anti_join(mf1, mf2)
#> Joining, by = "x"
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.25.3 [:memory:]
#>       x
#>   <dbl>
#> 1    NA
#> 2     3
anti_join(collect(mf1), collect(mf2))
#> Joining, by = "x"
#> # A tibble: 1 x 1
#>       x
#>   <dbl>
#> 1     3

^{Created on 2019-02-06 by the reprex package (v0.2.1.9000)}

[hadley]

Could provide sql_is_distinct(con, x, y) generic and then use that in sql_join_tbls() when na_matches is TRUE. I think we'd want to preserve the existing behaviour (clearly documented) and then allow people to choose the R behaviour if desired.