compute fails with JDBC connections because it calls dbSendQuery instead of dbSendUpdate

[shearerpmm]

The compute function throws an error when used with JDBC database connections. This happens because it uses dbSendQuery instead of dbSendUpdate, the appropriate method for data manipulation type SQL statements that don't return anything.

The commands below should provide everything you need to convince yourself of this. It's almost completely reproducible except you have to set up your own database and RJDBC connection. This issue has a method for doing that using docker.

# [not shown - set up your own RJDBC connection object]

library(tidyverse)
library(dbplyr)

# test compute() ---------------------------------------------------------

tbl(con, sql('select 1'))  %>% compute()

Error in `dplyr::db_save_query()`:
! Can't save query to "dbplyr_001".
Caused by error in `dbSendQuery()`:
! Unable to retrieve JDBC result set
  JDBC ERROR: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query "CREATE TEMPORARY TABLE 
"dbplyr_001" AS
SELECT *
FROM (select 1) "q01"
". Query not executed. 
  Statement: CREATE TEMPORARY TABLE 
"dbplyr_001" AS
SELECT *
FROM (select 1) "q01"
Run `rlang::last_error()` to see where the error occurred.

# test dbSendQuery() ---------------------------------------------------------
 
RJDBC::dbSendQuery(con, "CREATE TEMPORARY TABLE mytest as select 1")

Error in RJDBC::dbSendQuery(con, "CREATE TEMPORARY TABLE mytest as select 1") : 
  Unable to retrieve JDBC result set
  JDBC ERROR: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query "CREATE TEMPORARY TABLE mytest as select 1". Query not executed. 
  Statement: CREATE TEMPORARY TABLE mytest as select 1

# test dbSendUpdate() - no error ---------------------------------------------------------

RJDBC::dbSendUpdate(con, "CREATE TEMPORARY TABLE mytest as select 1")

Root Cause Analysis

I'm having some difficulty understanding what package(s) are responsible for the wrong method ultimately getting called. I think this is the key: we have this call chain

DBI::dbExecute -> DBI::dbSendStatement -> RJDBC::dbSendQuery

Which should instead be

DBI::dbExecute -> DBI::dbSendStatement -> RJDBC::dbSendUpdate

The DBI documentation says

dbSendStatement() comes with a default implementation that simply forwards to dbSendQuery(), to support backends that only implement the latter.

... and RJDBC does not implement dbSendStatement.

Tracing the error

Following up on the error from compute, if we trace through the stack of method calls, at bottom we find dbSendQuery being used to send the CREATE TABLE statement to the database:

> rlang::last_trace()
<error/rlang_error>
Error in `dplyr::db_save_query()`:
! Can't save query to "dbplyr_001".
Caused by error in `dbSendQuery()`:
! Unable to retrieve JDBC result set
  JDBC ERROR: [Vertica][JDBC](11300) A ResultSet was expected but not generated from query "CREATE TEMPORARY TABLE 
"dbplyr_001" AS
SELECT *
FROM (select 1) "q01"
". Query not executed. 
  Statement: CREATE TEMPORARY TABLE 
"dbplyr_001" AS
SELECT *
FROM (select 1) "q01"
---
Backtrace:
     ▆
  1. ├─base::source("~/.active-rstudio-document", echo = TRUE)
  2. │ ├─base::withVisible(eval(ei, envir))
  3. │ └─base::eval(ei, envir)
  4. │   └─base::eval(ei, envir)
  5. ├─x %>% head() %>% compute() at ~/.active-rstudio-document:10:0
  6. ├─dplyr::compute(.)
  7. └─dbplyr:::compute.tbl_sql(.)
  8.   ├─dbplyr::db_compute(...)
  9.   └─dbplyr:::db_compute.DBIConnection(...)
 10.     └─dbplyr:::dbplyr_save_query(con, sql, table, temporary = temporary)
 11.       └─dbplyr:::dbplyr_fallback(con, "db_save_query", ...)
 12.         ├─rlang::eval_bare(expr((!!fun)(con, ...)))
 13.         ├─dplyr::db_save_query(con, ...)
 14.         └─dbplyr:::db_save_query.DBIConnection(con, ...)
 15.           └─base::tryCatch(...)
 16.             └─base (local) tryCatchList(expr, classes, parentenv, handlers)
 17.               └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
 18.                 └─value[[3L]](cond)
 19.                   └─cli::cli_abort("Can't save query to {.val {name}}.", parent = cnd)
 20.                     └─rlang::abort(...)
 
 
 # ----------------------------------------------------------------------------------------------------------------


> dbplyr:::db_save_query.DBIConnection
function (con, sql, name, temporary = TRUE, ...) 
{
    sql <- sql_query_save(con, sql, name, temporary = temporary, 
        ...)
    tryCatch(DBI::dbExecute(con, sql, immediate = TRUE), error = function(cnd) {
        cli_abort("Can't save query to {.val {name}}.", parent = cnd)
    })
    name
}
<bytecode: 0x7fb98dd38f50>
<environment: namespace:dbplyr>


# ----------------------------------------------------------------------------------------------------------------


> DBI::dbExecute
standardGeneric for "dbExecute" defined from package "DBI"

function (conn, statement, ...) 
standardGeneric("dbExecute")
<bytecode: 0x7fb9916689f0>
<environment: 0x7fb99166efd8>
Methods may be defined for arguments: conn, statement
Use  showMethods(dbExecute)  for currently available ones.


# ----------------------------------------------------------------------------------------------------------------


> findMethods(DBI::dbExecute)
An object of class  “listOfMethods” 
$`DBIConnection#character`
Method Definition:

function (conn, statement, ...) 
{
    rs <- dbSendStatement(conn, statement, ...)
    on.exit(dbClearResult(rs))
    dbGetRowsAffected(rs)
}
<bytecode: 0x7fb991665978>
<environment: namespace:DBI>

Signatures:
        conn            statement  
target  "DBIConnection" "character"
defined "DBIConnection" "character"

Slot arguments:
[1] "conn"      "statement"
Slot signatures:
[[1]]
[1] "DBIConnection" "character"    

Slot generic:
standardGeneric for "dbExecute" defined from package "DBI"

function (conn, statement, ...) 
standardGeneric("dbExecute")
<bytecode: 0x7fb9916689f0>
<environment: 0x7fb99166efd8>
Methods may be defined for arguments: conn, statement
Use  showMethods(dbExecute)  for currently available ones.


# ----------------------------------------------------------------------------------------------------------------



> DBI::dbSendStatement
standardGeneric for "dbSendStatement" defined from package "DBI"
  defined with value class: "DBIResult"

function (conn, statement, ...) 
{
    ans <- standardGeneric("dbSendStatement")
    .valueClassTest(ans, "DBIResult", "dbSendStatement")
}
<bytecode: 0x7fb9973b7938>
<environment: 0x7fb9973bc5c0>
Methods may be defined for arguments: conn, statement
Use  showMethods(dbSendStatement)  for currently available ones.

  
# ----------------------------------------------------------------------------------------------------------------

  
> showMethods(DBI::dbSendStatement)
Function: dbSendStatement (package DBI)
conn="DBIConnection", statement="character"
conn="JDBCConnection", statement="sql"
    (inherited from: conn="DBIConnection", statement="character")


# ----------------------------------------------------------------------------------------------------------------


> findMethods(DBI::dbSendStatement)
An object of class  “listOfMethods” 
$`DBIConnection#character`
Method Definition:

function (conn, statement, ...) 
{
    dbSendQuery(conn, statement, ...)
}
<bytecode: 0x7fb9973af388>
<environment: namespace:DBI>

Signatures:
        conn            statement  
target  "DBIConnection" "character"
defined "DBIConnection" "character"

Slot arguments:
[1] "conn"      "statement"
Slot signatures:
[[1]]
[1] "DBIConnection" "character"    

Slot generic:
standardGeneric for "dbSendStatement" defined from package "DBI"
  defined with value class: "DBIResult"

function (conn, statement, ...) 
{
    ans <- standardGeneric("dbSendStatement")
    .valueClassTest(ans, "DBIResult", "dbSendStatement")
}
<bytecode: 0x7fb9973b7938>
<environment: 0x7fb9973bc5c0>
Methods may be defined for arguments: conn, statement
Use  showMethods(dbSendStatement)  for currently available ones.


# ----------------------------------------------------------------------------------------------------------------


> findMethods(DBI::dbSendQuery)
An object of class  “listOfMethods” 
$`JDBCConnection#character`
Method Definition:

function (conn, statement, ...) 
{
    .local <- function (conn, statement, ..., list = NULL) 
    {
        statement <- as.character(statement)[1L]
        if (isTRUE(as.logical(grepl("^\\{(call|\\?= *call)", 
            statement)))) {
            s <- .jcall(conn@jc, "Ljava/sql/CallableStatement;", 
                "prepareCall", statement, check = FALSE)
            .verify.JDBC.result(s, "Unable to execute JDBC callable statement", 
                statement = statement)
            .fillStatementParameters(s, list(...), list)
            r <- .jcall(s, "Ljava/sql/ResultSet;", "executeQuery", 
                check = FALSE)
            .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", 
                statement = statement)
        }
        else if (length(list(...)) || length(list)) {
            s <- .jcall(conn@jc, "Ljava/sql/PreparedStatement;", 
                "prepareStatement", statement, check = FALSE)
            .verify.JDBC.result(s, "Unable to execute JDBC prepared statement", 
                statement = statement)
            .fillStatementParameters(s, list(...), list)
            r <- .jcall(s, "Ljava/sql/ResultSet;", "executeQuery", 
                check = FALSE)
            .verify.JDBC.result(r, "Unable to retrieve JDBC result set", 
                statement = statement)
        }
        else {
            s <- .jcall(conn@jc, "Ljava/sql/Statement;", "createStatement")
            .verify.JDBC.result(s, "Unable to create simple JDBC statement", 
                statement = statement)
            r <- .jcall(s, "Ljava/sql/ResultSet;", "executeQuery", 
                as.character(statement)[1], check = FALSE)
            .verify.JDBC.result(r, "Unable to retrieve JDBC result set", 
                statement = statement)
        }
        md <- .jcall(r, "Ljava/sql/ResultSetMetaData;", "getMetaData", 
            check = FALSE)
        .verify.JDBC.result(md, "Unable to retrieve JDBC result set meta data in dbSendQuery", 
            statement = statement)
        new("JDBCResult", jr = r, md = md, stat = s, env = new.env(parent = emptyenv()))
    }
    .local(conn, statement, ...)
}
<bytecode: 0x7fb999b76b70>
<environment: namespace:RJDBC>

Signatures:
        conn             statement  
target  "JDBCConnection" "character"
defined "JDBCConnection" "character"

Slot arguments:
[1] "conn"      "statement"
Slot signatures:
[[1]]
[1] "JDBCConnection" "character"     

Slot generic:
standardGeneric for "dbSendQuery" defined from package "DBI"
  defined with value class: "DBIResult"

function (conn, statement, ...) 
{
    ans <- standardGeneric("dbSendQuery")
    .valueClassTest(ans, "DBIResult", "dbSendQuery")
}
<bytecode: 0x7fb9973d6748>
<environment: 0x7fb9973db078>
Methods may be defined for arguments: conn, statement
Use  showMethods(dbSendQuery)  for currently available ones.

[shearerpmm]

After some deeper digging I've realized this is not a problem for dbplyr to solve. This will be solved when RJDBC implements a method for DBI::sendStatement which calls RJDBC::dbSendUpdate instead of forwarding to the default RJDBC::dbSendQuery.

I've opened an issue on RJDBC to track this.