Redundant notFound exception when calling DBI::dbExecute("drop table ..")

[valentas-kurauskas]

When calling drop table, drop view, etc commands for an existing table (view, etc) through DBI::dbExecute, the command is completed, but an exception is thrown. This makes it necessary to surround each such query with try() as a workaround.

---

con <- DBI::dbConnect(bigrquery::bigquery(), project="myProject", dataset="myDataset")
#priority parameter is ignored
DBI::dbExecute(con, "drop table myDataset.myTable")
#Error : Not found: Table myDataset.myTable [notFound]
DBI::dbDisconnect(con)

[meztez]

After execution, it is trying to retrieve the numbers of rows from the table listed in the job meta.

From

bigrquery/R/dbi-result.R

Line 11 in ff5c22e

nrow <- bq_table_nrow(tb)

$configuration$query$destinationTable
$configuration$query$destinationTable$projectId
[1] "projectId"

$configuration$query$destinationTable$datasetId
[1] "datasetId"

$configuration$query$destinationTable$tableId
[1] "tableId"
...
$statistics$query$statementType
[1] "DROP_TABLE"

$statistics$query$ddlOperationPerformed
[1] "DROP"

I see a couple ways to avoid this error, the more obvious is to condition on the statementType.

But you could always use the more straight forward method DBI::dbRemoveTable.

[hadley]

Minimal reprex:

library(bigrquery)

bq_auth(email = 'hadley@rstudio.com')

con <- DBI::dbConnect(bigrquery::bigquery(), project = bq_test_project())
DBI::dbExecute(con, "drop table myDataset.myTable")
#> Error: Not found: Dataset gargle-169921:myDataset was not found in location US [notFound]
#Error : Not found: Table myDataset.myTable [notFound]
DBI::dbDisconnect(con)

^{Created on 2020-05-01 by the reprex package (v0.3.0)}

[hadley]

Better reprex:

library(bigrquery)

ds <- bq_test_dataset()
con <- DBI::dbConnect(bigrquery::bigquery(), 
  project = ds$project,
  dataset = ds$dataset
)

DBI::dbExecute(con, "DROP TABLE IF EXISTS foo")
#> Error: Not found: Table gargle-169921:TESTING_mblhqtzrzw.foo [notFound]

^{Created on 2020-05-05 by the reprex package (v0.3.0)}