Join fails to record "suffixed" vars

[pnacht]

If I run the following trivial example, I get the expected output:

library(dplyr)
library(dtplyr)
library(data.table)

dt1 <- lazy_dt(data.table(a = 1:5, b = 6:10))
dt2 <- lazy_dt(data.table(a = letters[1:5], b = 6:10))

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  as.data.table()
>     b a.x a.y
> 1:  6   1   a
> 2:  7   2   b
> 3:  8   3   c
> 4:  9   4   d
> 5: 10   5   e

Note that the conflicting columns a are properly managed, using the standard dplyr format of adding .x and .y suffixes.

However, if I now try to drop one of the columns:

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  select(
    -a.y
  ) %>%
  as.data.table()
> Error in is_character(x) : object 'a.y' not found

Details

Interestingly, if I try to select one of the a columns (select(a.x)), I get the same error, but... if I instead try select(a) (selecting a column which shouldn't really exist anymore), I get the following output:

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  select(
    a
  ) %>%
  as.data.table()
>    a.b
> 1:   1
> 2:   2
> 3:   3
> 4:   4
> 5:   5

where the selected column is clearly dt1$a, but for some reason the given column name is a.b. (if I try select(a.b), I get the same object not found error).

Meanwhile, if I try to drop a, both a columns are dropped:

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  select(
    -a
  ) %>%
  as.data.table()
>     b
> 1:  6
> 2:  7
> 3:  8
> 4:  9
> 5: 10

So, how can I use select with joins where the tables have conflicting (and not joined-by) columns?

EDIT:

As mentioned in some answers, I can obviously execute the lazy evaluation before the select, which works. However, it throws a warning (since I'd like to keep my object as a data.table, not a data.frame) so it doesn't seem to be the intended method:

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  as.data.table() %>%
  select(
    -a.x
  )
>     b a.y
> 1:  6   a
> 2:  7   b
> 3:  8   c
> 4:  9   d
> 5: 10   e
> Warning message:
> You are using a dplyr method on a raw data.table, which will call the data 
> frame implementation, and is likely to be inefficient.
> * 
> * To suppress this message, either generate a data.table translation with
> * `lazy_dt()` or convert to a data frame or tibble with
> * `as.data.frame()`/`as_tibble()`.

[hadley]

Can you please provide a minimal reproducible example using the reprex package? The goal of a reprex is to make it as easy as possible for me to recreate your problem so that I can fix it. If you've never made a minimal reprex before, there is lots of good advice here.

[pnacht]

Here are my examples, now run through reprex:

The basic case simply merges two lazy_dts with conflicting column names and works just fine, adding .x and .y suffixes to the conflicting columns:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(dtplyr)
library(data.table)
#> 
#> Attaching package: 'data.table'
#> The following objects are masked from 'package:dplyr':
#> 
#>     between, first, last

dt1 <- lazy_dt(data.table(a = 1:5, b = 6:10))
dt2 <- lazy_dt(data.table(a = letters[1:5], b = 6:10))

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  as.data.table()
#>     b a.x a.y
#> 1:  6   1   a
#> 2:  7   2   b
#> 3:  8   3   c
#> 4:  9   4   d
#> 5: 10   5   e

^{Created on 2019-11-22 by the reprex package (v0.3.0)}

If I now try to remove one of the conflicting columns, I get an error:

suppressPackageStartupMessages({
  library(dplyr)
  library(dtplyr)
  library(data.table)
})

dt1 <- lazy_dt(data.table(a = 1:5, b = 6:10))
dt2 <- lazy_dt(data.table(a = letters[1:5], b = 6:10))

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  select(
    -a.y
  ) %>%
  as.data.table()
#> Error in is_character(x): object 'a.y' not found

The same applies to selecting one of the conflicting columns (select(a.y)).

Oddly, if I now try selecting the conflicting columns with their original, un-suffixed name (select(a)), I now (as opposed to what I stated in my OP) get an error:

suppressPackageStartupMessages({
  library(dplyr)
  library(dtplyr)
  library(data.table)
})

dt1 <- lazy_dt(data.table(a = 1:5, b = 6:10))
dt2 <- lazy_dt(data.table(a = letters[1:5], b = 6:10))

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  select(
    a
  ) %>%
  as.data.table()
#> Error in eval(jsub, SDenv, parent.frame()): object 'a' not found

However, if I instead try to drop the conflicting columns using their unsuffixed name (select(-a)), both are dropped:

suppressPackageStartupMessages({
  library(dplyr)
  library(dtplyr)
  library(data.table)
})

dt1 <- lazy_dt(data.table(a = 1:5, b = 6:10))
dt2 <- lazy_dt(data.table(a = letters[1:5], b = 6:10))

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  select(
    -a
  ) %>%
  as.data.table()
#>     b
#> 1:  6
#> 2:  7
#> 3:  8
#> 4:  9
#> 5: 10

My current workaround is to push the as.data.table() call before the select. This works, but throws a warning (since I'd rather keep my tables as data.tables, not data.frames):

suppressPackageStartupMessages({
  library(dplyr)
  library(dtplyr)
  library(data.table)
})

dt1 <- lazy_dt(data.table(a = 1:5, b = 6:10))
dt2 <- lazy_dt(data.table(a = letters[1:5], b = 6:10))

dt1 %>%
  left_join(
    dt2,
    by = "b"
  ) %>%
  as.data.table() %>%
  select(
    -a.x
  )
#> Warning: You are using a dplyr method on a raw data.table, which will call the
#> * data frame implementation, and is likely to be inefficient.
#> * 
#> * To suppress this message, either generate a data.table translation with
#> * `lazy_dt()` or convert to a data frame or tibble with
#> * `as.data.frame()`/`as_tibble()`.
#>     b a.y
#> 1:  6   a
#> 2:  7   b
#> 3:  8   c
#> 4:  9   d
#> 5: 10   e

[hadley]

Looks like the problem is that step_join(), because it doesn't update vars correctly.

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

dt1 <- lazy_dt(data.frame(a = 1:2, b = 6:7))
dt2 <- lazy_dt(data.frame(a = letters[1:2], b = 6:7))

dt <- dt1 %>% left_join(dt2, by = "b") 

dt %>% collect()
#> # A tibble: 2 x 3
#>       b   a.x a.y  
#>   <int> <int> <fct>
#> 1     6     1 a    
#> 2     7     2 b
dt %>% tbl_vars()
#> <dplyr:::vars>
#> [1] "a" "b"

^{Created on 2019-12-24 by the reprex package (v0.3.0)}