filter in can not take empty vector of values on PostgreSQL

[ghost]

@JohnMount commented on Feb 22, 2018, 3:59 PM UTC:

filter %in% c() throws on PostgreSQL.

``` r 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

db <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
host = 'localhost',
port = 5432,
user = 'johnmount',
password = '')
table <- dplyr::copy_to(db,
data.frame(idx = 1:5))

works

vals <- c(1, 2)
table %>% filter(idx %in% vals)

## # Source:   lazy query [?? x 1]
## # Database: postgres 10.0.2 [johnmount@localhost:5432/johnmount]
##     idx
##   <int>
## 1     1
## 2     2

throws

vals <- c()
table %>% filter(idx %in% vals)

## Error in postgresqlExecStatement(conn, statement, ...): RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at or near ")"
## LINE 3: WHERE ("idx" IN ())
##                          ^
## )

DBI::dbDisconnect(db)

## [1] TRUE

</details>

```</details>

*This issue was moved by [romainfrancois](https://github.com/romainfrancois) from [tidyverse/dplyr#3375](https://github.com/tidyverse/dplyr/issues/3375).*

[ghost]

@hadley commented on May 20, 2018, 2:15 PM UTC:

Minimal reprex:

library(dplyr, warn.conflicts = FALSE)

lf1 <- dbplyr::lazy_frame(x = 1:5, src = dbplyr::simulate_dbi())
lf1 %>% 
  filter(x %in% c()) %>%
  show_query()
#> <SQL> SELECT *
#> FROM "df"
#> WHERE ("x" IN ())

I'm not sure if there's anything we can do here. I guess we could translate x %in% c() to FALSE?

[ghost]

@mgirlich commented on May 21, 2018, 5:41 AM UTC:

@hadley it would make sense imho and be the same behaviour as for local dataframes. I already did a PR for this behaviour.