cast "as.double" and "as.numeric" to DOUBLE PRECISION or FLOAT instead of NUMERIC for Redshift/PostgreSQL

[kmishra9]

Very similar issue to #379 and (I assume) the associated fix at #388. Casting a string, using as.numeric() and as.double() in Redshift coerces to an integer.

Given a column x, with the string "0.88", the output value is 0. The SQL translation is CAST("x" AS NUMERIC) in both cases.

Using sql('cast(x AS DOUBLE PRECISION)') or sql('cast(x AS FLOAT)') directly outputs the expected 0.88

[hadley]

Could you please create a reprex, following the advice at https://dbplyr.tidyverse.org/articles/reprex.html?

[kmishra9]

Here you go:

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

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

# Does not work as expected
lf %>% mutate(x = as.double(x))
#> <SQL>
#> SELECT CAST(`x` AS NUMERIC) AS `x`
#> FROM `df`
lf %>% mutate(x = as.numeric(x))
#> <SQL>
#> SELECT CAST(`x` AS NUMERIC) AS `x`
#> FROM `df`

# Works as expected
lf %>% mutate(x = sql('cast(x AS DOUBLE PRECISION)'))
#> <SQL>
#> SELECT cast(x AS DOUBLE PRECISION) AS `x`
#> FROM `df`
lf %>% mutate(x = sql('cast(x AS FLOAT)'))
#> <SQL>
#> SELECT cast(x AS FLOAT) AS `x`
#> FROM `df`

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

[hadley]

Fixed in ccdda24