dbExistsTable() throws error for DB2 temporary tables in version 1.6.0

[nattomi]

dbExistsTable(conn, Id(schema = "SESSION", table = "MY_TEMP"))

throws

Error in `$<-.data.frame`(`*tmp*`, table_remarks, value = NA_character_) : 
  replacement has 1 row, data has 0
Calls: print ... tryCatchList -> tryCatchOne -> doTryCatch -> $<- -> $<-.data.frame`

in version 1.6.0 when applied to DB2 global temporary tables (SESSION.* tables).

In version 1.5.0 dbExistsTable returns FALSE in the same scenario.

Please refer to https://github.com/nattomi/odbc-dbexiststable-issue for a reproducible example.

Whether dbExistsTable should theoretically return FALSE or TRUE is not quite clear, because temporary tables are also not visible in the system catalog (syscat,tables). However, it should definitely not throw.

[detule]

Hey - thanks for the report!

If you have time, can you check if this PR yields results that are more along what you would expect?

Thanks again.

[nattomi]

Hi, it seems like it got better, but I could still trigger the error:

> dbExecute(conn, "DECLARE GLOBAL TEMPORARY TABLE SESSION.my_temp (id INT, name VARCHAR(100)) ON COMMIT PRESERVE ROWS")
[1] -1
> dbExecute(conn, "INSERT INTO SESSION.MY_TEMP (id, name) VALUES (1, 'Alice'), (2, 'Bob')")
[1] 2
> dbGetQuery(conn, "select * from SESSION.MY_TEMP")
  ID  NAME
1  1 Alice
2  2   Bob
> dbExistsTable(conn, Id(schema = "SESSION", table = "MY_TEMP"))
[1] TRUE
> dbExistsTable(conn, Id(schema = "SESSION", table = "MY_TEM"))
Error in `$<-.data.frame`(`*tmp*`, table_remarks, value = NA_character_) : 
  replacement has 1 row, data has 0
> dbExistsTable(conn, "SESSION.MY_TEMP") 
Error in `$<-.data.frame`(`*tmp*`, table_remarks, value = NA_character_) : 
  replacement has 1 row, data has 0
> dbExistsTable(conn, SQL('"SESSION"."MY_TEMP"'))
[1] TRUE
> dbExistsTable(conn, "MY_TEMP")
[1] TRUE
> dbExistsTable(conn, "MY_TEM")
Error in `$<-.data.frame`(`*tmp*`, table_remarks, value = NA_character_) : 
  replacement has 1 row, data has 0

[detule]

Hey @nattomi Thanks for the report. Strange, when using the branch from that PR, I can't replicate your last run.

 ┃  > conn
 ┃  <OdbcConnection> db2inst1@DB2
 ┃    Database: SAMPLE
 ┃    DB2/LINUXX8664 Version: 11.05.0800
 ┃  > dbExecute(conn, "DECLARE GLOBAL TEMPORARY TABLE SESSION.my_temp (id INT, name VARCHAR(100)) ON COMMIT PRESERVE ROWS")
 ┃  [1] -1
 ┃  > dbExecute(conn, "INSERT INTO SESSION.MY_TEMP (id, name) VALUES (1, 'Alice'), (2, 'Bob')")
 ┃  [1] 2
 ┃  > dbGetQuery(conn, "select * from SESSION.MY_TEMP")
 ┃    ID  NAME
 ┃  1  1 Alice
 ┃  2  2   Bob
 ┃  > dbExistsTable(conn, Id(schema = "SESSION", table = "MY_TEMP"))
 ┃  [1] TRUE
 ┃  > dbExistsTable(conn, Id(schema = "SESSION", table = "MY_TEM"))
 ┃  [1] FALSE
 ┃  > dbExistsTable(conn, "SESSION.MY_TEMP")
 ┃  [1] FALSE
 ┃  > dbExistsTable(conn, SQL('"SESSION"."MY_TEMP"'))
 ┃  [1] TRUE
 ┃  > dbExistsTable(conn, "MY_TEMP")
 ┃  [1] TRUE
 ┃  > dbExistsTable(conn, "MY_TEM")
 ┃  [1] FALSE

I am a little bit at a loss in trying to figure out what's different. Do you have any idea what's going on?

[nattomi]

@detule, I finally had the time to give it another try. I tried it with 2 different DB2 instances / drivers and in both cases I could get the exact same results as you. No error was thrown this time. Thank you very much, it looks very promising!

[detule]

Excellent, I'll close this issue now as the fix is in main.