SQL compilation error causes R session to hang - [Snowflake] driver

[vivas89]

R session hangs when attempting to send a query to Snowflake. It seems that the Snowflake driver errors out but the DBI package does not know how to recover gracefully from that error, so it hangs for ~ 5 - 10 minutes.

This has been reproduced on multiple environments with different versions of Snowflake driver:

Test Environment

Snowflake version 3.1.2.1002 on 22.04.5 LTS (Jammy Jellyfish)
Snowflake version 3.3.3.3010 on rockylinux8
Snowflake version 3.3.3.3010 on macos ARM Sequoia 15.4.1

Reproducible code

snowflake_conn <- DBI::dbConnect(
  drv = odbc::snowflake(),
  tracing = 6, # most verbose log level
  CURLVerboseMode = TRUE,
  LogPath = "snowflake_driver_logs"
)

query <- paste0(
  "SELECT STATECODE FROM AIR_QUALITY_DATA_UNITED_STATES.PUBLIC.AIR_QUALITY WHERE COUNTYCODE IN (",
  paste(rep('1', 200001), collapse = ", "),
  ")"
)

x <- DBI::dbGetQuery(conn = snowflake_conn, statement = query)

Error in the logs shows the following:

SQL compilation error: error line 1 at position 92 maximum number of expressions in a list exceeded, expected at most 200,000, got 200,001

If I wait long enough, the console errors out with the following:

... 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
  1, 1, 1)'
ℹ From nanodbc/nanodbc.cpp:1726.
Run `rlang::last_trace()` to see where the error occurred.

It fails here: https://github.com/r-dbi/odbc/blob/main/src/nanodbc/nanodbc.cpp#L1726

[robert-norberg]

If I run the query with immediate = FALSE:

x <- DBI::dbGetQuery(conn = snowflake_conn, statement = query, immediate = FALSE)

and wait long enough, I get the message:

...
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
  1, 1, 1)'
ℹ From nanodbc/nanodbc.cpp:1544.
Run `rlang::last_trace()` to see where the error occurred.

Which points here instead, another instance of NANODBC_THROW_DATABASE_ERROR(stmt_, SQL_HANDLE_STMT).