str_replace_all broken for redshift

[kmishra9]

Code that ran fine under past versions of dbplyr threw some errors. I'm assuming the changes made in the newest dbplyr changelog (1.4.3) probably created the issue? The error is in the generated SQL, which provides a 'g' to the position parameter of the regexp_replace function in Redshift.

Error: Error: Failed to prepare query: ERROR: invalid input syntax for integer: "g"

Reprex:

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

lf <- dbplyr::lazy_frame(x = "0.88", con = dbplyr::simulate_postgres())

lf %>% mutate(x_cleaned = str_replace_all(string = x, pattern = "\\.", replacement = ""))
#> <SQL>
#> SELECT `x`, REGEXP_REPLACE(`x`, '\.', '', 'g') AS `x_cleaned`
#> FROM `df`

^{Created on 2020-05-08 by the reprex package (v0.3.0)}

[kmishra9]

Separately, redshift seems to have an issue with the tick marks around x and responds with the error: Error: Failed to prepare query: ERROR: operator does not exist: ` character varying HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.. Removing them in the raw sql translation that I'm using to get around this error for now allows the code to run correctly (as does using double quotes instead of backticks)

[hadley]

str_replace_all() did not behave correctly in the previous version; it only replaced a single match. This appears to be because the Postgres and Redshift implementation of REGEXP_REPLACE differ in whether or not they perform 1 or all replacements.