Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

addDaysQuery produces erroneous query for Redshift driver #7

Open
krystian8207 opened this issue Jun 25, 2024 · 0 comments · May be fixed by #8
Open

addDaysQuery produces erroneous query for Redshift driver #7

krystian8207 opened this issue Jun 25, 2024 · 0 comments · May be fixed by #8

Comments

@krystian8207
Copy link

krystian8207 commented Jun 25, 2024 •
edited
Loading

Describe the bug
The number argument passed to addDaysQuery and minusDaysQuery is treated by dbplyr parser as double/numeric.
As a result for Redshift driver we get DATEADD(DAY, <double>, "variable") statement.
Such statement produces error as DATEADD is not supporting doubles as a second argument for Redshift.

To Reproduce

# unfortunately the needs an active Redshift connection in order to reproduce
db <- DBI::dbConnect(
  RPostgres::Redshift(),
  dbname   = Sys.getenv("REDSHIFT_DBNAME"),
  host     = Sys.getenv("REDSHIFT_HOST"),
  port     = Sys.getenv("REDSHIFT_PORT"),
  user     = Sys.getenv("REDSHIFT_USERNAME"),
  password = Sys.getenv("REDSHIFT_PASSWORD")
)
cdm <- CDMConnector::cdmFromCon(
  con = db,
  cdmSchema = c(schema = Sys.getenv("CDM_SCHEMA")),
  writeSchema = c(schema = Sys.getenv("CDM_WRITE_SCHEMA"), prefix = "test"),
  cdmName = Sys.getenv("REDSHIFT_DBNAME")
)
query_expr <- IncidencePrevalence:::addDaysQuery(cdm, "index_date", 1, "day", "expr")
my_tbl <- dplyr::tbl(db, "mytbl")
inc_date_tbl <- my_tbl |> dplyr::mutate(inc_date = !!query_expr$expr) 
inc_date_tbl |> dplyr::show_query()
#> <SQL>
#> SELECT "mytbl".*, DATEADD(DAY, 1.0, "index_date") AS "inc_date"
#> FROM "mytbl"

inc_date_tbl |> dplyr::collect()
#> Error in `dplyr::collect()`:
#> ! Failed to collect lazy table.
#> Caused by error:
#> ! Failed to prepare query: ERROR:  function pg_catalog.date_add("unknown", numeric, date) does not exist
#> HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

Desktop (please complete the following information):

> sessionInfo()
R version 4.2.1 (2022-06-23)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 20.04.5 LTS

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3
LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/liblapack.so.3

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8   
 [6] LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices datasets  utils     methods   base     

other attached packages:
[1] dbplyr_2.5.0    dplyr_1.1.4     DBI_1.2.1       RPostgres_1.4.5 odbc_1.3.5     

loaded via a namespace (and not attached):
 [1] Rcpp_1.0.12                 pillar_1.9.0                compiler_4.2.1              CDMConnector_1.4.0          tools_4.2.1                
 [6] PatientProfiles_1.1.0       bit_4.0.5                   jsonlite_1.8.8              lubridate_1.9.3             lifecycle_1.0.4            
[11] tibble_3.2.1                timechange_0.2.0            pkgconfig_2.0.3             rlang_1.1.3                 cli_3.6.2                  
[16] rstudioapi_0.16.0           clock_0.7.0                 withr_3.0.0                 generics_0.1.3              vctrs_0.6.5                
[21] hms_1.1.3                   rprojroot_2.0.3             bit64_4.0.5                 tidyselect_1.2.1            glue_1.7.0                 
[26] here_1.0.1                  IncidencePrevalence_0.7.3   R6_2.5.1                    fansi_1.0.6                 tzdb_0.4.0                 
[31] tidyr_1.3.1                 purrr_1.0.2                 blob_1.2.4                  magrittr_2.0.3              omopgenerics_0.2.1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Force passing integer value to addDaysQuery and minusDaysQuery. r-world-devs/IncidencePrevalence
1 participant
@krystian8207