grep like commands are not translated correctly

[roelhogervorst]

I can't get the string detection to work through dbplyr:

tbl(con,"results") %>% 
   filter(grepl("position",name))

'ParseException:SQLparser:42000!SELECT: no such binary operator 'grepl(char,clob)''

or the stringr package:

tbl(con, "results" %>% 
   filter(str_detect(pattern = "position",string = name))

'ParseException:SQLparser:42000!SELECT: no such binary operator 'instr(clob,char)''

While it does work if I create the SQL:

DBI::dbGetQuery(con,"select * from results where name like '%position%'")

Where is this translation implemented? Is this a DBI issue, dbplyr issue? Where should I look?

[roelhogervorst]

holy cow this is actually very difficult to find out, note to self: look either here: https://rud.is/b/2019/04/10/lost-in-sql-translation-charting-dbplyr-mapped-sql-function-support-across-all-backends/ or https://apps.fishandwhistle.net/archives/1503