date_build sql translation on redshift is incorrect

[ablack3]

The translation of date_build is slightly incorrect on redshift.

dbplyr/tests/testthat/test-backend-redshift.R

Line 66 in 8f2fcb0

expect_equal(test_translate_sql(date_build(2020, 1, 1)), sql("TO_DATE(CAST(2020.0 AS TEXT) || '-' CAST(1.0 AS TEXT) || '-' || CAST(1.0 AS TEXT)), 'YYYY-MM-DD')"))

con <- DBI::dbConnect(RPostgres::Redshift(),
                      dbname   = Sys.getenv("CDM5_REDSHIFT_DBNAME"),
                      host     = Sys.getenv("CDM5_REDSHIFT_HOST"),
                      port     = Sys.getenv("CDM5_REDSHIFT_PORT"),
                      user     = Sys.getenv("CDM5_REDSHIFT_USER"),
                      password = Sys.getenv("CDM5_REDSHIFT_PASSWORD"))

# current translation
sql <- "SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' CAST(1.0 AS TEXT) || '-' || CAST(1.0 AS TEXT)), 'YYYY-MM-DD') AS dt"
cat(sql)
#> SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' CAST(1.0 AS TEXT) || '-' || CAST(1.0 AS TEXT)), 'YYYY-MM-DD') AS dt
DBI::dbGetQuery(con, sql)
#> Error: Failed to prepare query : ERROR:  syntax error at or near "CAST"
#> LINE 1: SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' CAST(1.0 AS TEXT)...
#>                                                    ^

# corrected sql
sql <- "SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' || CAST(1.0 AS TEXT) || '-' || CAST(1.0 AS TEXT), 'YYYY-MM-DD') AS dt"
cat(sql)
#> SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' || CAST(1.0 AS TEXT) || '-' || CAST(1.0 AS TEXT), 'YYYY-MM-DD') AS dt
DBI::dbGetQuery(con, sql)
#>           dt
#> 1 2020-01-01

DBI::dbDisconnect(con)

^{Created on 2024-06-05 with reprex v2.1.0}