dbplyr not working with list input

[arthur.t]

Hi folks. I would appreciate any help with this. When I filter() using objects inside a list, dbplyr doesn't seem to properly convert into the query. Here is a minimal example:

library(dbplyr)
mf <- memdb_frame(x = letters)

# this works
mf %>% filter(x %in% "a") %>% collect()  

# this works
achr <- "a"
mf %>% filter(x %in% achr) %>% collect()

# this doesn't work
alist <- list(a = "a")
mf %>% filter(x %in% alist$a) %>% collect()  

[mara]

Adding a reprex below that replaces collect() with show_query() for easier comparison of output:

library(dplyr)
library(dbplyr)

mf <- memdb_frame(x = letters)

# this works
mf %>% filter(x %in% "a") %>% show_query()
#> <SQL>
#> SELECT *
#> FROM `dbplyr_001`
#> WHERE (`x` IN ('a'))

# this works
achr <- "a"
mf %>% filter(x %in% achr) %>% show_query()
#> <SQL>
#> SELECT *
#> FROM `dbplyr_001`
#> WHERE (`x` IN ('a'))

# this doesn't work
alist <- list(a = "a")
mf %>% filter(x %in% alist$a) %>% show_query()
#> <SQL>
#> SELECT *
#> FROM `dbplyr_001`
#> WHERE (`x` IN ('a' AS `a`).`a`)

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

[mishabalyasin]

You need to explicitly evaluate alist$a in local R session with !! or you can save it to a different variable with something like db_var <- alist$a. Both approaches will work:

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(dbplyr)
#> 
#> Attaching package: 'dbplyr'
#> The following objects are masked from 'package:dplyr':
#> 
#>     ident, sql

mf <- memdb_frame(x = letters)

# this works
alist <- list(a = "a")
mf %>% filter(x %in% !!alist$a) %>% show_query()
#> <SQL>
#> SELECT *
#> FROM `dbplyr_001`
#> WHERE (`x` IN ('a'))

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

This is because alist$a actually makes sense in modern DBs where you can routinely store JSON in columns and you can access it with this syntax.