translate_sql, as.character in combination with %in%

[jessekps]

dbplyr
version 1.1.0

If I use %in% in combination with as.character, translate_sql generates SQL that is not valid for sqlite

library(RSQLite)
library(dbplyr)
db = dbConnect(SQLite(), ':memory:')

translate_sql(booklet_id %in% as.character(1:4), con=db )
#> <SQL> "booklet_id" IN CAST((1, 2, 3, 4) AS TEXT)

In postgres and possibly other dbms this would be accepted but not in sqlite where it should be:

"booklet_id" IN( CAST(1 AS TEXT), CAST(2 AS TEXT), CAST(3 AS TEXT), CAST(4 AS TEXT))

(running it through partial_eval and translate_sql_ gives the exact same problem)

Thanks for the very nice package by the way!

[krlmlr]

Thanks, confirmed. I'm seeing on PostgreSQL:

> echo "SELECT '1' in cast((1, 2, 3, 4) as text)" | psql
ERROR:  syntax error at or near "cast"
LINE 1: SELECT '1' in cast((1, 2, 3, 4) as text)
> echo "SELECT '1' in (cast(1 as text), cast(2 as text))" | psql
 ?column? 
----------
 t
(1 row)

Seems we need to adapt it there, too (at least).

[edgararuiz-zz]

Hi @jessekps / @krlmlr - I don't see a way for dbplyr to know to override passing a translated vector function, such as as.character(), without any input from the user.

Having said that, there is a way to make sure that the R function is evaluated prior sending it to the query, and that's by using rlang 's !!:

library(RSQLite)                                            
library(dbplyr)                                             
library(rlang)                                              
                                                            
db <- dbConnect(SQLite(), ':memory:')                       
                                                            
translate_sql(booklet_id %in% !! as.character(1:4), con=db )
#> <SQL> `booklet_id` IN ('1', '2', '3', '4')
                                                            
dbDisconnect(db)    

[jessekps]

Ok, thanks for looking into it anyway, much appreciated. I know about the !! but unfortunately I'm passing through an r statement from users of my package so I have no control over that. And since I'm trying to take some of the sql complexity away from the user, I don't want to replace it by them having to learn about !!.

Currently I'm fixing the translate_sql output with regular expressions. It works fine for my package but it's not a structural solution you would want I expect.

[hadley]

This is currently out of scope for dbplyr sql translation.

[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/