dbExistsTable() in R odbc says table exists when it shouldn't

Question

Issue and Expected Result

Link to Github Issue

I am trying to drop a table in SQL Server 2017. When I inspect the database, it appears as if the table has disappeared. However, according to odbc, the table still exists. In my example, I am writing a table to a default schema. I then copy the table from default schema into a new table in a new schema. I then drop the table in the default schema. When I run dbExistsTable(), it returns TRUE, though.

Server

SQL Server 2017

Reproducible Example

dbWriteTable(con, "iris", iris)
dbExecute(con, "SELECT * INTO [schema].iris FROM iris")
dbExecute(con, "DROP TABLE iris")

dbExistsTable(con, "iris") # returns TRUE, should be FALSE
dbListTables(con) # iris appears here, it should not appear here

EDIT 1

I will note this error does not appear in the following example leading me to believe something is happening when the table is being copied into another schema.

dbWriteTable(con, "iris", iris)
dbExecute(con, "SELECT * INTO iris2 FROM iris")
dbExecute(con, "DROP TABLE iris")

dbExistsTable(con, "iris") # <- returns false as expected
dbListTables(con) # <- iris table does not appear here

EDIT 2

After dropping the newly created table in the non-default schema, the issue is resolved. This behavior, though, is wrong. I should have to specify in dbExistsTable() the schema name to find that the table exists, no?

Session Info

devtools::session_info()
─ Session info ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 setting  value                       
 version  R version 3.6.0 (2019-04-26)
 os       Ubuntu 18.04.2 LTS          
 system   x86_64, linux-gnu           
 ui       RStudio                     
 language (EN)                        
 collate  en_US.UTF-8                 
 ctype    en_US.UTF-8                 
 tz       America/New_York            
 date     2019-06-18                  

─ Packages ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 package     * version date       lib source        
 assertthat    0.2.1   2019-03-21 [1] CRAN (R 3.5.3)
 backports     1.1.4   2019-04-10 [1] CRAN (R 3.5.3)
 bit           1.1-14  2018-05-29 [1] CRAN (R 3.5.3)
 bit64         0.9-7   2017-05-08 [1] CRAN (R 3.5.3)
 blob          1.1.1   2018-03-25 [1] CRAN (R 3.5.3)
 callr         3.2.0   2019-03-15 [1] CRAN (R 3.5.3)
 cli           1.1.0   2019-03-19 [1] CRAN (R 3.5.3)
 colorspace    1.4-1   2019-03-18 [1] CRAN (R 3.5.3)
 crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.3)
 DBI         * 1.0.0   2018-05-02 [1] CRAN (R 3.5.3)
 dbplyr      * 1.4.2   2019-06-17 [1] CRAN (R 3.6.0)
 desc          1.2.0   2018-05-01 [1] CRAN (R 3.5.3)
 devtools      2.0.2   2019-04-08 [1] CRAN (R 3.5.3)
 digest        0.6.19  2019-05-20 [1] CRAN (R 3.6.0)
 dplyr       * 0.8.1   2019-05-14 [1] CRAN (R 3.6.0)
 evaluate      0.14    2019-05-28 [1] CRAN (R 3.6.0)
 forcats     * 0.4.0   2019-02-17 [1] CRAN (R 3.6.0)
 fs            1.3.1   2019-05-06 [1] CRAN (R 3.6.0)
 ggplot2     * 3.2.0   2019-06-16 [1] CRAN (R 3.6.0)
 glue          1.3.1   2019-03-12 [1] CRAN (R 3.5.3)
 gtable        0.3.0   2019-03-25 [1] CRAN (R 3.5.3)
 here        * 0.1     2017-05-28 [1] CRAN (R 3.6.0)
 hms           0.4.2   2018-03-10 [1] CRAN (R 3.5.3)
 htmltools     0.3.6   2017-04-28 [1] CRAN (R 3.5.3)
 knitr         1.23    2019-05-18 [1] CRAN (R 3.6.0)
 lazyeval      0.2.2   2019-03-15 [1] CRAN (R 3.5.3)
 magrittr      1.5     2014-11-22 [1] CRAN (R 3.5.3)
 memoise       1.1.0   2017-04-21 [1] CRAN (R 3.5.3)
 munsell       0.5.0   2018-06-12 [1] CRAN (R 3.5.3)
 odbc        * 1.1.6   2018-06-09 [1] CRAN (R 3.5.3)
 pillar        1.4.1   2019-05-28 [1] CRAN (R 3.6.0)
 pkgbuild      1.0.3   2019-03-20 [1] CRAN (R 3.5.3)
 pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.5.3)
 pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.5.3)
 prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.5.3)
 processx      3.3.1   2019-05-08 [1] CRAN (R 3.6.0)
 ps            1.3.0   2018-12-21 [1] CRAN (R 3.5.3)
 purrr       * 0.3.2   2019-03-15 [1] CRAN (R 3.5.3)
 R6            2.4.0   2019-02-14 [1] CRAN (R 3.5.3)
 Rcpp          1.0.1   2019-03-17 [1] CRAN (R 3.5.3)
 readr       * 1.3.1   2018-12-21 [1] CRAN (R 3.6.0)
 remotes       2.0.4   2019-04-10 [1] CRAN (R 3.5.3)
 rlang         0.3.4   2019-04-07 [1] CRAN (R 3.5.3)
 rmarkdown   * 1.13    2019-05-22 [1] CRAN (R 3.6.0)
 rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.6.0)
 rstudioapi    0.10    2019-03-19 [1] CRAN (R 3.5.3)
 scales        1.0.0   2018-08-09 [1] CRAN (R 3.5.3)
 sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.5.3)
 tibble        2.1.3   2019-06-06 [1] CRAN (R 3.6.0)
 tidyr       * 0.8.3   2019-03-01 [1] CRAN (R 3.5.3)
 tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.5.3)
 usethis       1.5.0   2019-04-07 [1] CRAN (R 3.5.3)
 withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.3)
 xfun          0.7     2019-05-14 [1] CRAN (R 3.6.0)

Answer 1

It is likely that dbExistsTable() is doing something similar to "Select 1 from information_schema.tables where table_name = 'iris'" under the covers, which would find [schema].iris and return true, whether or not [default].iris has been deleted.