Redshift `round()` can't return decimal places

[owenjonesuob]

Redshift's numeric (== decimal) type has a default scale (number of decimal places) of 0, so naively casting values to numeric can lead to surprising results.

The Redshift backend already guards against this, by instead casting to float:

dbplyr/R/backend-redshift.R

Line 40 in f4cef23

as.numeric = sql_cast("FLOAT"),

However the Postgres backend, which the Redshift backend inherits from, uses a custom function for its translation of round(), which contains an explicit cast to numeric:

postgres_round <- function(x, digits = 0L) {
  digits <- as.integer(digits)
  sql_expr(round(((!!x)) %::% numeric, !!digits))
}

This means we end up with the following SQL translation:

translate_sql(round(1.234, 1), con = simulate_redshift())
#> <SQL> ROUND((1.234) :: numeric, 1)

This always:

• Implicitly rounds to 0 decimal places, via the cast to numeric
• Then, explicitly rounds the result to the specified number of decimal places, meaning we end up with at best 0dp

Hopefully an easy fix by overriding round in the Redshift translator with a small round_redshift() function - I'll add a pull request shortly!