filter generates buggy SQL when comparing with list element

[misea]

When using tbl via dbplyr, filter generates correct SQL when comparing a column with an R scalar variable, but when using $ syntax to access value in a list, buggy sql is generated. The reprex below produces errors both with Postgres and SQLite connections, though more of the generated SQL is visible with Postgres

library(DBI)
library(dbplyr)

init_table <- function(con) {
  # dbExecute(con, "DROP TABLE a")
  dbExecute(con, "CREATE TABLE a (a_id INTEGER, a_value VARCHAR)")
  dbExecute(con, "INSERT INTO a (a_id, a_value) VALUES (1, 'value 1')")
}

test_filter <- function(con) {
  tbl_a <- tbl(con, "a")
  # This works fine
  id <- 1
  tbl_a %>% filter(a_id == id)
  # This blows up
  l <- list(id = 1)
  tbl_a %>% filter(a_id == l$id)
}

slCon <- DBI::dbConnect(RSQLite::SQLite())
init_table(slCon)
test_filter(slCon)
# SQLite: Error in `collect()`:
# ! Failed to collect lazy table.
# Caused by error:
# ! near "AS": syntax error

# pgCon=dbConnect(RPostgres::Postgres(), host = "...", user = "...")
# init_table(pgCon)
# test_filter(pgCon)
# Error in `collect()`:
# ! Failed to collect lazy table.
# Failed to prepare query: ERROR:  syntax error at or near "AS"
# LINE 3: WHERE ("a_id" = (1.0 AS "id").1.0)

Something is wrong with the SQL quoting in this case.

[hadley]

Hmmmm, it looks like we're looking up id in the local environment, which doesn't make much sense to me:

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

lf <- lazy_frame(a_id = 1, a_value = "value 1")

id <- 10000
l <- list(id = 1)
lf |> filter(a_id == l$id)
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`a_id` = (1.0 AS `id`).10000.0)

^{Created on 2023-11-02 with reprex v2.0.2}

You can work around the problem by explicitly requesting local computation:

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

lf <- lazy_frame(a_id = 1, a_value = "value 1")

l <- list(id = 1)
lf |> filter(a_id == local(l$id))
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`a_id` = 1.0)

^{Created on 2023-11-02 with reprex v2.0.2}