Cannot use DBI::Id in dplyr::tbl on Oracle with odbc

[ablack3]

While dbplyr::in_schema can be used to create table references to an Oracle table in a schema, DBI::Id cannot. I would expect the behavior to be similar.

con <- DBI::dbConnect(odbc::odbc(), "OracleODBC-19")

person <- dplyr::tbl(con, DBI::Id(schema = "CDMV5", table = "PERSON"))

observation_period <- dplyr::tbl(con,  DBI::Id(schema = "CDMV5", table = "OBSERVATION_PERIOD"))

dplyr::inner_join(person, observation_period, by = "PERSON_ID")
#> Error in `collect()`:
#> ! Failed to collect lazy table.
#> Caused by error:
#> ! nanodbc/nanodbc.cpp:1655: 00000: [Oracle][ODBC][Ora]ORA-01741: illegal zero-length identifier
#>                                                               
#> <SQL> 'SELECT *
#> FROM (
#>   SELECT
#>     """CDMV5"".""PERSON""".*,
#>     "OBSERVATION_PERIOD_ID",
#>     "OBSERVATION_PERIOD_START_DATE",
#>     "OBSERVATION_PERIOD_END_DATE",
#>     "PERIOD_TYPE_CONCEPT_ID"
#>   FROM ("CDMV5"."PERSON") """CDMV5"".""PERSON"""
#>   INNER JOIN ("CDMV5"."OBSERVATION_PERIOD") """CDMV5"".""OBSERVATION_PERIOD"""
#>     ON ("""CDMV5"".""PERSON"""."PERSON_ID" = """CDMV5"".""OBSERVATION_PERIOD"""."PERSON_ID")
#> ) "q01"
#> FETCH FIRST 11 ROWS ONLY'

#> Backtrace:
#>      ▆
#>   1. ├─base::tryCatch(...)
#>   2. │ └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>   3. │   ├─base (local) tryCatchOne(...)
#>   4. │   │ └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   5. │   └─base (local) tryCatchList(expr, names[-nh], parentenv, handlers[-nh])
#>   6. │     └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>   7. │       └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   8. ├─base::withCallingHandlers(...)
#>   9. ├─base::saveRDS(...)
#>  10. ├─base::do.call(...)
#>  11. ├─base (local) `<fn>`(...)
#>  12. └─global `<fn>`(input = base::quote("nerdy-ram_reprex.R"))
#>  13.   └─rmarkdown::render(input, quiet = TRUE, envir = globalenv(), encoding = "UTF-8")
#>  14.     └─knitr::knit(knit_input, knit_output, envir = envir, quiet = quiet)
#>  15.       └─knitr:::process_file(text, output)
#>  16.         ├─base::withCallingHandlers(...)
#>  17.         ├─knitr:::process_group(group)
#>  18.         └─knitr:::process_group.block(group)
#>  19.           └─knitr:::call_block(x)
#>  20.             └─knitr:::block_exec(params)
#>  21.               └─knitr:::eng_r(options)
#>  22.                 ├─knitr:::in_input_dir(...)
#>  23.                 │ └─knitr:::in_dir(input_dir(), expr)
#>  24.                 └─knitr (local) evaluate(...)
#>  25.                   └─evaluate::evaluate(...)
#>  26.                     └─evaluate:::evaluate_call(...)
#>  27.                       ├─evaluate (local) handle(...)
#>  28.                       │ └─base::try(f, silent = TRUE)
#>  29.                       │   └─base::tryCatch(...)
#>  30.                       │     └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>  31.                       │       └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>  32.                       │         └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>  33.                       ├─base::withCallingHandlers(...)
#>  34.                       ├─base::withVisible(value_fun(ev$value, ev$visible))
#>  35.                       └─knitr (local) value_fun(ev$value, ev$visible)
#>  36.                         └─knitr (local) fun(x, options = options)
#>  37.                           ├─base::withVisible(knit_print(x, ...))
#>  38.                           ├─knitr::knit_print(x, ...)
#>  39.                           └─rmarkdown:::knit_print.tbl_sql(x, ...)
#>  40.                             ├─context$df_print(x)
#>  41.                             └─dbplyr:::print.tbl_sql(x)
#>  42.                               ├─dbplyr:::cat_line(format(x, ..., n = n, width = width, n_extra = n_extra))
#>  43.                               │ ├─base::cat(paste0(..., "\n"), sep = "")
#>  44.                               │ └─base::paste0(..., "\n")
#>  45.                               ├─base::format(x, ..., n = n, width = width, n_extra = n_extra)
#>  46.                               └─pillar:::format.tbl(x, ..., n = n, width = width, n_extra = n_extra)
#>  47.                                 └─pillar:::format_tbl(...)
#>  48.                                   └─pillar::tbl_format_setup(...)
#>  49.                                     ├─pillar:::tbl_format_setup_dispatch(...)
#>  50.                                     └─pillar:::tbl_format_setup.tbl(...)
#>  51.                                       └─pillar:::df_head(x, n + 1)
#>  52.                                         ├─base::as.data.frame(head(x, n))
#>  53.                                         └─dbplyr:::as.data.frame.tbl_sql(head(x, n))
#>  54.                                           ├─base::as.data.frame(collect(x, n = n))
#>  55.                                           ├─dplyr::collect(x, n = n)
#>  56.                                           └─dbplyr:::collect.tbl_sql(x, n = n)
#>  57.                                             └─base::tryCatch(...)
#>  58.                                               └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>  59.                                                 └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>  60.                                                   └─value[[3L]](cond)
#>  61.                                                     └─cli::cli_abort("Failed to collect lazy table.", parent = cnd)
#>  62.                                                       └─rlang::abort(...)

observation_period <- dplyr::tbl(con, dbplyr::in_schema("CDMV5", "OBSERVATION_PERIOD")) 

person <- dplyr::tbl(con, dbplyr::in_schema("CDMV5", "PERSON"))

dplyr::inner_join(person, observation_period, by = "person_id")
#> Error in `dplyr::inner_join()`:
#> ! Join columns in `x` must be present in the data.
#> ✖ Problem with `person_id`.

#> Backtrace:
#>     ▆
#>  1. ├─dplyr::inner_join(person, observation_period, by = "person_id")
#>  2. └─dbplyr:::inner_join.tbl_lazy(person, observation_period, by = "person_id")
#>  3.   └─dbplyr:::add_join(...)
#>  4.     └─dbplyr:::join_cols(...)
#>  5.       └─dbplyr:::check_join_vars(by$x, x_names, by$condition, "x", error_call = error_call)
#>  6.         └─cli::cli_abort(bullets, call = error_call)
#>  7.           └─rlang::abort(...)

DBI::dbDisconnect(con)

^{Created on 2023-03-07 with reprex v2.0.2}

Session info

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.2.2 (2022-10-31)
#>  os       macOS Big Sur ... 10.16
#>  system   x86_64, darwin17.0
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       America/New_York
#>  date     2023-03-07
#>  pandoc   2.19.2 @ /Applications/RStudio.app/Contents/Resources/app/quarto/bin/tools/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version    date (UTC) lib source
#>  bit           4.0.5      2022-11-15 [1] CRAN (R 4.2.0)
#>  bit64         4.0.5      2020-08-30 [1] CRAN (R 4.2.0)
#>  blob          1.2.3      2022-04-10 [1] CRAN (R 4.2.0)
#>  cli           3.6.0      2023-01-09 [1] CRAN (R 4.2.0)
#>  DBI           1.1.3      2022-06-18 [1] CRAN (R 4.2.0)
#>  dbplyr        2.3.0.9000 2023-02-14 [1] Github (tidyverse/dbplyr@9607491)
#>  digest        0.6.31     2022-12-11 [1] CRAN (R 4.2.0)
#>  dplyr         1.1.0      2023-01-29 [1] CRAN (R 4.2.0)
#>  ellipsis      0.3.2      2021-04-29 [1] CRAN (R 4.2.0)
#>  evaluate      0.20       2023-01-17 [1] CRAN (R 4.2.0)
#>  fansi         1.0.4      2023-01-22 [1] CRAN (R 4.2.0)
#>  fastmap       1.1.0      2021-01-25 [1] CRAN (R 4.2.0)
#>  fs            1.6.1      2023-02-06 [1] CRAN (R 4.2.0)
#>  generics      0.1.3      2022-07-05 [1] CRAN (R 4.2.0)
#>  glue          1.6.2      2022-02-24 [1] CRAN (R 4.2.0)
#>  hms           1.1.2      2022-08-19 [1] CRAN (R 4.2.0)
#>  htmltools     0.5.4      2022-12-07 [1] CRAN (R 4.2.0)
#>  knitr         1.42       2023-01-25 [1] CRAN (R 4.2.0)
#>  lifecycle     1.0.3      2022-10-07 [1] CRAN (R 4.2.0)
#>  magrittr      2.0.3      2022-03-30 [1] CRAN (R 4.2.0)
#>  odbc          1.3.3      2021-11-30 [1] CRAN (R 4.2.0)
#>  pillar        1.8.1      2022-08-19 [1] CRAN (R 4.2.0)
#>  pkgconfig     2.0.3      2019-09-22 [1] CRAN (R 4.2.0)
#>  purrr         1.0.1      2023-01-10 [1] CRAN (R 4.2.0)
#>  R6            2.5.1      2021-08-19 [1] CRAN (R 4.2.0)
#>  Rcpp          1.0.10     2023-01-22 [1] CRAN (R 4.2.0)
#>  reprex        2.0.2      2022-08-17 [1] CRAN (R 4.2.0)
#>  rlang         1.0.6      2022-09-24 [1] CRAN (R 4.2.0)
#>  rmarkdown     2.20       2023-01-19 [1] CRAN (R 4.2.0)
#>  rstudioapi    0.14       2022-08-22 [1] CRAN (R 4.2.0)
#>  sessioninfo   1.2.2      2021-12-06 [1] CRAN (R 4.2.0)
#>  tibble        3.1.8      2022-07-22 [1] CRAN (R 4.2.0)
#>  tidyselect    1.2.0      2022-10-10 [1] CRAN (R 4.2.0)
#>  utf8          1.2.3      2023-01-31 [1] CRAN (R 4.2.0)
#>  vctrs         0.5.2      2023-01-23 [1] CRAN (R 4.2.0)
#>  withr         2.5.0      2022-03-03 [1] CRAN (R 4.2.0)
#>  xfun          0.37       2023-01-31 [1] CRAN (R 4.2.0)
#>  yaml          2.3.7      2023-01-23 [1] CRAN (R 4.2.0)
#> 
#>  [1] /Library/Frameworks/R.framework/Versions/4.2/Resources/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

[mgirlich]

@ablack3
I did some changes that might already fix this issue. As I don't have an Oracle database to test with, I need some help here.
Can you check whether this works with the current dev version?