filter(var = var), unexpected behaviour

[michaelhogersosis]

Hi there,

Currently using dplyr 0.8.0.1 in combination with a postgres backend.

Suppose I run the following:
var <- '123'
tbl(dbCon, 'exampleTable') %>% dplyr::filter(var == var)
where the first var in filter is a database column and the second var is supposed to be the var I assigned in my R script. This then results in selecting the entire table as var = var is seen as true for every row.

I understand the logic behind the way it is executed, but perhaps a warning message would be nice, or preventing duplicate variables from being used at all within filter. As it stands, it may only produce erroneous behaviour, as one would never use filter to select an entire table.

Best regards,

Michael

[romatik]

First of all, you've probably meant var == var, not var = var.
Second of all, recent changes in dbplyr (second point here https://dbplyr.tidyverse.org/news/index.html#breaking-changes) mean that it is now up to you to control where things are evaluated. Here is an example of what I mean:

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
iris2 <- dbplyr::tbl_memdb(iris) 
iris2 %>% dplyr::filter(Species == Species) %>% dplyr::tally()
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.22.0 [:memory:]
#>       n
#>   <int>
#> 1   150

Species <- "setosa"
iris2 %>% dplyr::filter(Species == !!Species) %>% dplyr::tally()
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.22.0 [:memory:]
#>       n
#>   <int>
#> 1    50

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

There are more details and examples here - https://community.rstudio.com/t/dbplyr-dplyr-filter-worked-fine-in-r-3-5-1-but-fails-in-r-3-6-0-using-in-in-database-query/31664

You are right that most of the time it's a mistake to have code that is var == var. However, I'm not sure it's always a mistake, so maybe it won't be fixed directly anytime soon.

[michaelhogersosis]

Thank you for a possible solution. Changing the behavior indeed might have an unexpected effect on existing code..

I edited the post, it should show == as you mentioned.

[romainfrancois]

duplicate to #3937

[lock]

This old issue has been automatically locked. If you believe you have found a related problem, please file a new issue (with reprex) and link to this issue. https://reprex.tidyverse.org/