Skip to content

Instantly share code, notes, and snippets.

@infotroph

infotroph/gist:19513e684c97b576e24c8b1058b082ee

Last active January 28, 2018 19:44
Show Gist options
  • Save infotroph/19513e684c97b576e24c8b1058b082ee to your computer and use it in GitHub Desktop.
Save infotroph/19513e684c97b576e24c8b1058b082ee to your computer and use it in GitHub Desktop.
Download ZIP
Why not .data in sql filter?
Raw
gistfile1.R
library(tidyverse)
# local df works as expected with or without .data
mtcars %>% select(mpg) %>% filter(mpg > 33)
# mpg
# 1 33.9
mtcars %>% select(.data$mpg) %>% filter(.data$mpg > 33)
# mpg
# 1 33.9
con = DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, mtcars, "carsql", temporary=F)
# sql works as expected without .data
tbl(con, "carsql") %>% select(mpg) %>% filter(mpg > 33)
# # Source: lazy query [?? x 1]
# # Database: sqlite 3.19.3 []
# mpg
# <dbl>
# 1 33.9
# sql select works as expected with .data
tbl(con, "carsql") %>% select(.data$mpg) %>% filter(mpg > 33)
# # Source: lazy query [?? x 1]
# # Database: sqlite 3.19.3 []
# mpg
# <dbl>
# 1 33.9
# sql filter does not work with .data
tbl(con, "carsql") %>% select(.data$mpg) %>% filter(.data$mpg > 33)
# Error in eval_bare(call, env) : object '.data' not found
@infotroph
Copy link
Author 

devtools::session_info()

Session info ------------------------------------------------------------------
 setting  value                       
 version  R version 3.4.3 (2017-11-30)
 system   x86_64, darwin15.6.0        
 ui       X11                         
 language (EN)                        
 collate  en_US.UTF-8                 
 tz       Europe/Amsterdam            
 date     2018-01-22                  

Packages ----------------------------------------------------------------------
 package    * version date       source        
 assertthat   0.2.0   2017-04-11 CRAN (R 3.4.0)
 base       * 3.4.3   2017-12-07 local         
 bindr        0.1     2016-11-13 CRAN (R 3.4.0)
 bindrcpp     0.2     2017-06-17 CRAN (R 3.4.0)
 broom        0.4.3   2017-11-20 CRAN (R 3.4.3)
 cellranger   1.1.0   2016-07-27 CRAN (R 3.4.0)
 cli          1.0.0   2017-11-05 CRAN (R 3.4.2)
 colorspace   1.3-2   2016-12-14 CRAN (R 3.4.0)
 compiler     3.4.3   2017-12-07 local         
 crayon       1.3.4   2017-09-16 CRAN (R 3.4.1)
 datasets   * 3.4.3   2017-12-07 local         
 devtools     1.13.4  2017-11-09 CRAN (R 3.4.2)
 digest       0.6.14  2018-01-14 CRAN (R 3.4.3)
 dplyr      * 0.7.4   2017-09-28 cran (@0.7.4) 
 forcats    * 0.2.0   2017-01-23 CRAN (R 3.4.0)
 foreign      0.8-69  2017-06-22 CRAN (R 3.4.3)
 ggplot2    * 2.2.1   2016-12-30 CRAN (R 3.4.0)
 glue         1.2.0   2017-10-29 CRAN (R 3.4.2)
 graphics   * 3.4.3   2017-12-07 local         
 grDevices  * 3.4.3   2017-12-07 local         
 grid         3.4.3   2017-12-07 local         
 gtable       0.2.0   2016-02-26 CRAN (R 3.4.0)
 haven        1.1.1   2018-01-18 CRAN (R 3.4.3)
 hms          0.4.0   2017-11-23 CRAN (R 3.4.3)
 httr         1.3.1   2017-08-20 cran (@1.3.1) 
 jsonlite     1.5     2017-06-01 CRAN (R 3.4.0)
 lattice      0.20-35 2017-03-25 CRAN (R 3.4.3)
 lazyeval     0.2.1   2017-10-29 CRAN (R 3.4.2)
 lubridate    1.7.1   2017-11-03 CRAN (R 3.4.2)
 magrittr     1.5     2014-11-22 CRAN (R 3.4.0)
 memoise      1.1.0   2017-04-21 CRAN (R 3.4.0)
 methods      3.4.3   2017-12-07 local         
 mnormt       1.5-5   2016-10-15 CRAN (R 3.4.0)
 modelr       0.1.1   2017-07-24 CRAN (R 3.4.1)
 munsell      0.4.3   2016-02-13 CRAN (R 3.4.0)
 nlme         3.1-131 2017-02-06 CRAN (R 3.4.3)
 parallel     3.4.3   2017-12-07 local         
 pillar       1.1.0   2018-01-14 CRAN (R 3.4.3)
 pkgconfig    2.0.1   2017-03-21 CRAN (R 3.4.0)
 plyr         1.8.4   2016-06-08 CRAN (R 3.4.0)
 psych        1.7.8   2017-09-09 CRAN (R 3.4.1)
 purrr      * 0.2.4   2017-10-18 CRAN (R 3.4.2)
 R6           2.2.2   2017-06-17 CRAN (R 3.4.0)
 Rcpp         0.12.15 2018-01-20 CRAN (R 3.4.3)
 readr      * 1.1.1   2017-05-16 CRAN (R 3.4.0)
 readxl       1.0.0   2017-04-18 CRAN (R 3.4.0)
 reshape2     1.4.3   2017-12-11 CRAN (R 3.4.3)
 rlang        0.1.6   2017-12-21 CRAN (R 3.4.3)
 rstudioapi   0.7     2017-09-07 cran (@0.7)   
 rvest        0.3.2   2016-06-17 cran (@0.3.2) 
 scales       0.5.0   2017-08-24 cran (@0.5.0) 
 stats      * 3.4.3   2017-12-07 local         
 stringi      1.1.6   2017-11-17 cran (@1.1.6) 
 stringr    * 1.2.0   2017-02-18 CRAN (R 3.4.0)
 tibble     * 1.4.1   2017-12-25 CRAN (R 3.4.3)
 tidyr      * 0.7.2   2017-10-16 CRAN (R 3.4.2)
 tidyverse  * 1.2.1   2017-11-14 CRAN (R 3.4.2)
 tools        3.4.3   2017-12-07 local         
 utils      * 3.4.3   2017-12-07 local         
 withr        2.1.1   2017-12-19 CRAN (R 3.4.3)
 xml2         1.1.1   2017-01-24 CRAN (R 3.4.0)

@infotroph
Copy link
Author 

tbl(con, "carsql") %>% select(.data$mpg) %>% filter(as.name("mpg") > 33)
# Error in rsqlite_send_query(conn@ptr, statement) : near "AS": syntax error

tbl(con, "carsql") %>% select(.data$mpg) %>% filter(base::as.name("mpg") > 33)
# # Source: lazy query [?? x 1]
# # Database: sqlite 3.19.3 []
#     mpg
#   <dbl>
# 1  33.9

@infotroph
Copy link
Author

infotroph commented Jan 28, 2018 •
edited
Loading

Behavior doesn't change when I switch to dev versions of Tidyverse packages.

library(tidyverse)

# local df works as expected with or without .data
mtcars %>% select(mpg) %>% filter(mpg > 33)
#>    mpg
#> 1 33.9
mtcars %>% select(.data$mpg) %>% filter(.data$mpg > 33)
#>    mpg
#> 1 33.9

con = DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
copy_to(con, mtcars, "carsql", temporary=F)

# sql works as expected without .data
tbl(con, "carsql") %>% select(mpg) %>% filter(mpg > 33)
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.19.3 []
#>     mpg
#>   <dbl>
#> 1  33.9

# sql select works as expected with .data
tbl(con, "carsql") %>% select(.data$mpg) %>% filter(mpg > 33)
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.19.3 []
#>     mpg
#>   <dbl>
#> 1  33.9

# sql filter does not work with .data
tbl(con, "carsql") %>% select(.data$mpg) %>% filter(.data$mpg > 33)
#> Error: Column `mpg` not found in `.data`

devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.4.3 (2017-11-30)
#>  system   x86_64, darwin15.6.0        
#>  ui       X11                         
#>  language (EN)                        
#>  collate  en_US.UTF-8                 
#>  tz       Europe/Amsterdam            
#>  date     2018-01-28
#> Packages -----------------------------------------------------------------
#>  package    * version    date       source                              
#>  assertthat   0.2.0      2017-04-11 CRAN (R 3.4.0)                      
#>  backports    1.1.2      2017-12-13 CRAN (R 3.4.3)                      
#>  base       * 3.4.3      2017-12-07 local                               
#>  bindr        0.1        2016-11-13 CRAN (R 3.4.0)                      
#>  bindrcpp   * 0.2        2017-06-17 CRAN (R 3.4.0)                      
#>  bit          1.1-12     2014-04-09 CRAN (R 3.4.0)                      
#>  bit64        0.9-7      2017-05-08 CRAN (R 3.4.0)                      
#>  blob         1.1.0      2017-06-17 CRAN (R 3.4.0)                      
#>  broom        0.4.2      2018-01-28 Github (tidyverse/broom@4d0c83a)    
#>  cellranger   1.1.0      2016-07-27 CRAN (R 3.4.0)                      
#>  cli          1.0.0      2017-11-05 CRAN (R 3.4.2)                      
#>  colorspace   1.3-2      2016-12-14 CRAN (R 3.4.0)                      
#>  compiler     3.4.3      2017-12-07 local                               
#>  crayon       1.3.4      2017-09-16 CRAN (R 3.4.1)                      
#>  datasets   * 3.4.3      2017-12-07 local                               
#>  DBI          0.7        2017-06-18 cran (@0.7)                         
#>  dbplyr       1.2.0      2018-01-28 Github (tidyverse/dbplyr@ea093ca)   
#>  devtools     1.13.4     2017-11-09 CRAN (R 3.4.2)                      
#>  digest       0.6.14     2018-01-14 CRAN (R 3.4.3)                      
#>  dplyr      * 0.7.4.9000 2018-01-28 Github (tidyverse/dplyr@3f91e1e)    
#>  evaluate     0.10.1     2017-06-24 CRAN (R 3.4.1)                      
#>  forcats    * 0.2.0.9000 2018-01-28 Github (tidyverse/forcats@fdde458)  
#>  foreign      0.8-69     2017-06-22 CRAN (R 3.4.3)                      
#>  ggplot2    * 2.2.1.9000 2018-01-28 Github (tidyverse/ggplot2@401511e)  
#>  glue         1.2.0      2017-10-29 CRAN (R 3.4.2)                      
#>  graphics   * 3.4.3      2017-12-07 local                               
#>  grDevices  * 3.4.3      2017-12-07 local                               
#>  grid         3.4.3      2017-12-07 local                               
#>  gtable       0.2.0      2016-02-26 CRAN (R 3.4.0)                      
#>  haven        1.1.1      2018-01-18 CRAN (R 3.4.3)                      
#>  hms          0.4.1      2018-01-28 Github (tidyverse/hms@e68d386)      
#>  htmltools    0.3.6      2017-04-28 cran (@0.3.6)                       
#>  httr         1.3.1      2017-08-20 cran (@1.3.1)                       
#>  jsonlite     1.5        2017-06-01 CRAN (R 3.4.0)                      
#>  knitr        1.18       2017-12-27 CRAN (R 3.4.3)                      
#>  lattice      0.20-35    2017-03-25 CRAN (R 3.4.3)                      
#>  lazyeval     0.2.1      2017-10-29 CRAN (R 3.4.2)                      
#>  lubridate    1.7.1      2018-01-28 Github (tidyverse/lubridate@2e11bc9)
#>  magrittr     1.5.0      2018-01-28 Github (tidyverse/magrittr@0a76de2) 
#>  memoise      1.1.0      2017-04-21 CRAN (R 3.4.0)                      
#>  methods    * 3.4.3      2017-12-07 local                               
#>  mnormt       1.5-5      2016-10-15 CRAN (R 3.4.0)                      
#>  modelr       0.1.1      2018-01-28 Github (tidyverse/modelr@69381e8)   
#>  munsell      0.4.3      2016-02-13 CRAN (R 3.4.0)                      
#>  nlme         3.1-131    2017-02-06 CRAN (R 3.4.3)                      
#>  parallel     3.4.3      2017-12-07 local                               
#>  pillar       1.1.0      2018-01-14 CRAN (R 3.4.3)                      
#>  pkgconfig    2.0.1      2017-03-21 CRAN (R 3.4.0)                      
#>  plyr         1.8.4      2016-06-08 CRAN (R 3.4.0)                      
#>  psych        1.7.8      2017-09-09 CRAN (R 3.4.1)                      
#>  purrr      * 0.2.4.9000 2018-01-28 Github (tidyverse/purrr@62b135a)    
#>  R6           2.2.2      2017-06-17 CRAN (R 3.4.0)                      
#>  Rcpp         0.12.15    2018-01-20 CRAN (R 3.4.3)                      
#>  readr      * 1.2.0      2018-01-28 Github (tidyverse/readr@2e1fa9a)    
#>  readxl       1.0.0      2017-04-18 CRAN (R 3.4.0)                      
#>  reshape2     1.4.3      2017-12-11 CRAN (R 3.4.3)                      
#>  rlang        0.1.6.9003 2018-01-28 Github (tidyverse/rlang@73d8f50)    
#>  rmarkdown    1.8        2017-11-17 CRAN (R 3.4.2)                      
#>  rprojroot    1.3-2      2018-01-03 CRAN (R 3.4.3)                      
#>  RSQLite      2.0        2017-06-19 CRAN (R 3.4.1)                      
#>  rvest        0.3.2      2016-06-17 cran (@0.3.2)                       
#>  scales       0.5.0.9000 2018-01-28 Github (hadley/scales@d767915)      
#>  stats      * 3.4.3      2017-12-07 local                               
#>  stringi      1.1.6      2017-11-17 cran (@1.1.6)                       
#>  stringr    * 1.2.0.9000 2018-01-28 Github (tidyverse/stringr@c8bbc0d)  
#>  tibble     * 1.4.2      2018-01-28 Github (tidyverse/tibble@7ab7327)   
#>  tidyr      * 0.7.2.9000 2018-01-28 Github (tidyverse/tidyr@74bd48f)    
#>  tidyselect   0.2.3      2017-11-06 CRAN (R 3.4.2)                      
#>  tidyverse  * 1.2.1      2018-01-28 Github (tidyverse/tidyverse@03ccf9c)
#>  tools        3.4.3      2017-12-07 local                               
#>  utf8         1.1.3      2018-01-03 CRAN (R 3.4.3)                      
#>  utils      * 3.4.3      2017-12-07 local                               
#>  withr        2.1.1.9000 2018-01-28 Github (jimhester/withr@df18523)    
#>  xml2         1.2.0      2018-01-24 cran (@1.2.0)                       
#>  yaml         2.1.16     2017-12-12 CRAN (R 3.4.3)

Created on 2018-01-28 by the reprex package (v0.1.1.9000).

@infotroph
Copy link
Author

Possibly related to dplyr issue 2930.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment