Running DUS.R fails at `GENERATE DRUG CONCEPTS`

[festerman]

• Latest checked-in version of DUS Code, plus fixes acc to issue #53 (why are they not checked in, btw?)
• We are running SQL Server 2019, with the latest MS ODBC driver.

• Created a temporary table named #dbplyr_5TW1sH6Qlm
  Error in `db_copy_to()` at dbplyr/R/verb-copy-to.R:83:5:
  ! Can't copy data to table "#dbplyr_5TW1sH6Qlm".
  Caused by error in `dplyr::db_write_table()` at dbplyr/R/db-io.R:79:7:
  ! Can't write table table "#dbplyr_5TW1sH6Qlm".
  Caused by error in `name@name[["table"]]`:
  ! subscript out of bounds

• Backtrace:
     ▆
  1. ├─base::source(here("DUS.R"))
  2. │ ├─base::withVisible(eval(ei, envir))
  3. │ └─base::eval(ei, envir)
  4. │   └─base::eval(ei, envir)
  5. ├─base::source(here("drugs.R")) at MegaStudy/DUS Code/DUS.R:137:1
  6. │ ├─base::withVisible(eval(ei, envir))
  7. │ └─base::eval(ei, envir)
  8. │   └─base::eval(ei, envir)
  9. ├─CodelistGenerator::getDrugIngredientCodes(...) at MegaStudy/DUS Code/drugs.R:2:1
  10. │ └─CodelistGenerator:::fetchBatchedDescendants(...) at CodelistGenerator/R/drugCodes.R:226:5
  11. │   └─CodelistGenerator::getDescendants(...) at CodelistGenerator/R/drugCodes.R:309:5
  12. │     └─CodelistGenerator:::getDescendantsAndAncestor(...) at CodelistGenerator/R/vocabUtilities.R:353:5
  13. │       └─... %>% dplyr::left_join(cdm$concept, by = "concept_id") at CodelistGenerator/R/vocabUtilities.R:394:3
  14. ├─dplyr::left_join(., cdm$concept, by = "concept_id") at magrittr/R/pipe.R:136:3
  15. ├─dplyr::rename(., concept_id = "descendant_concept_id") at dplyr/R/join.R:300:3
  16. ├─dplyr::inner_join(...) at dplyr/R/rename.R:64:3
  17. ├─omopgenerics:::inner_join.cdm_table(...) at dplyr/R/join.R:258:3
  18. │ ├─dplyr::inner_join(x, ...) at omopgenerics/R/overwriteClasses.R:137:3
  19. │ └─dbplyr:::inner_join.tbl_lazy(x, ...) at dplyr/R/join.R:258:3
  20. │   └─dbplyr:::add_join(...) at dbplyr/R/verb-joins.R:89:3
  21. │     ├─dplyr::auto_copy(x, y, copy = copy, indexes = if (auto_index) list(by$y)) at dbplyr/R/verb-joins.R:356:3
  22. │     └─dbplyr:::auto_copy.tbl_sql(x, y, copy = copy, indexes = if (auto_index) list(by$y)) at dplyr/R/copy-to.R:59:3
  23. │       ├─dplyr::copy_to(...) at dbplyr/R/verb-copy-to.R:103:3
  24. │       └─dbplyr:::copy_to.src_sql(...) at dplyr/R/copy-to.R:32:3
  25. │         ├─dbplyr::db_copy_to(...) at dbplyr/R/verb-copy-to.R:83:5
  26. │         └─dbplyr:::db_copy_to.DBIConnection(...) at dbplyr/R/db-io.R:54:3
  27. │           ├─dbplyr:::with_transaction(...) at dbplyr/R/db-io.R:74:3
  28. │           │ └─base::withCallingHandlers(...) at dbplyr/R/db-io.R:233:3
  29. │           ├─dplyr::db_write_table(...) at dbplyr/R/db-io.R:79:7
  30. │           └─dbplyr:::db_write_table.DBIConnection(...) at dplyr/R/dbplyr.R:90:3
  31. │             ├─base::withCallingHandlers(...) at dbplyr/R/db-io.R:189:3
  32. │             ├─DBI::dbWriteTable(...) at dbplyr/R/db-io.R:189:3
  33. │             └─odbc::dbWriteTable(...) at DBI/R/13-dbWriteTable.R:49:9
  34. │               └─odbc (local) .local(conn, name, value, ...)
  35. │                 ├─DBI::dbExistsTable(conn, name) at odbc/R/Table.R:54:5
  36. │                 └─odbc::dbExistsTable(conn, name)
  37. │                   ├─DBI::dbExistsTable(...) at odbc/R/Table.R:223:5
  38. │                   └─odbc::dbExistsTable(...)
  39. │                     └─name@name[["table"]] %in% ... at odbc/R/Table.R:210:5
  40. └─dbplyr (local) `<fn>`(`<sbscOOBE>`) at odbc/R/Table.R:210:5
  41.   └─cli::cli_abort(msg, parent = cnd) at dbplyr/R/db-io.R:202:7
  42.     └─rlang::abort(...) at cli/R/rlang.R:45:3

• The error message is not SQL Server. At this point in the process there is upwards of 60 temporary tables in the session.
• It's not the size of the input list:

• > concept_drugs <- getDrugIngredientCodes(
  +     cdm,
  +     name = c("tenecteplase"),
  + doseForm = NULL,
  + ingredientRange = c(1, 1),
  + withConceptDetails = FALSE
  + )
  
  Created a temporary table named #dbplyr_x63yeNQWOO
  Error in `db_copy_to()` at dbplyr/R/verb-copy-to.R:83:5:
  ! Can't copy data to table "#dbplyr_x63yeNQWOO".
  Caused by error in `dplyr::db_write_table()` at dbplyr/R/db-io.R:79:7:
  ! Can't write table table "#dbplyr_x63yeNQWOO".
  Caused by error in `name@name[["table"]]`:
  ! subscript out of bounds

• ... and it's not getDrugIngredientCodes itself

• > getDrugIngredientCodes(cdm = test_cdm, name = "Adalimumab")
  $adalimumab
  [1] 10 13
  > 

• Trying now with an later version odbc package -- but results will take some time, since changing a package req. a R Studio restart -- w/a DB session disconnect, and to get to the same situation (w/50+ temp tables) requires rerunning from the start (some modularization would simplify debugging ... ;) ).

[tiozab]

Let us know if you need our help. You can always re-run the DUS_1point4.R file "line by line" with fewer drugs / indications, or we can guide you through it. Did you successfully deploy the new renv as well from #53 ? it has the odbc package version "Version": "1.3.5",

[festerman]

Tested different version combinations. Base is the renv.lock from @tiozab in #53 (except I used renv 1.07, and removed the RPostgres package). Tried it on R 4.3.2 and 4.4.0, and with M$ ODBC driver 17 or 18. Also retooled, to run it on our HPC cluster -- no RStudio.

Crashes as above with odbc 1.3.5 -- in any combination of R and M$ driver. Works with R package odbc 1.5.0 -- likewise in any combination of R and driver.

So it looks very much like a bug in odbc 1.3.5 ...

[tiozab]

Thanks @festerman for the report. @edward-burn any idea?

[edward-burn]

@festerman if it is working with odbc 1.5 then that's great, did the whole study work with that?