dbGetQuery with adbcsnowflake crash

[klin333]

dbGetQuery with ADBC Snowflake connection (adbcsnowflake) crashes.

A quick fix is here: https://github.com/klin333/adbi/tree/fix_snowflake
However, I think this will just hide and work-around the problem without solving the root cause. Also it fails a bunch of tests.

library(DBI)

uri <-  "SECRET"
adbi_con <- dbConnect(adbi::adbi("adbcsnowflake::adbcsnowflake"), uri = uri)
adbi_con
# <AdbiConnection>
# Driver name: ADBC Snowflake Driver - Go
# Driver version: (unknown or development build)
# Driver arrow version: v14.0.0
# Vendor name: Snowflake

dbWriteTable(adbi_con, "SWISS", datasets::swiss) # works
dbGetQuery(adbi_con, "SELECT * from SWISS") # crashes
# Error in adbcdrivermanager::adbc_statement_get_parameter_schema(stmt) :

sessionInfo()

R version 4.3.2 (2023-10-31 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 11 x64 (build 22621)

Matrix products: default


locale:
[1] LC_COLLATE=English_Australia.utf8  LC_CTYPE=English_Australia.utf8    LC_MONETARY=English_Australia.utf8
[4] LC_NUMERIC=C                       LC_TIME=English_Australia.utf8    

time zone: Australia/Sydney
tzcode source: internal

attached base packages:
[1] graphics  grDevices utils     methods   base     

other attached packages:
[1] DBI_1.1.3

loaded via a namespace (and not attached):
 [1] bit_4.0.5                compiler_4.3.2           tools_4.3.2              rstudioapi_0.15.0        adbcsnowflake_0.8.0.9000
 [6] stats_4.3.2              nanoarrow_0.3.0.1        bit64_4.0.5              adbcdrivermanager_0.8.0  adbi_0.0.2              
[11] renv_1.0.3   

[krlmlr]

Thanks. Is this still a problem with more recent package versions?

CC @paleolimbot @lidavidm .

[paleolimbot]

Thank you for reporting!

As Kirill noted, number of these packages have been updated...in particular install.packages(c("nanoarrow", "adbcdrivermanager")) would be helpful since I believe adbcdrivermanager (for R) had a few very large bugs prior to 0.9.0. You should be able to install the most recent Snowflake driver release by installing a Go compiler and running pak::pak("apache/arrow-adbc/r/adbcsnowflake").

It would also be useful to see if this crashes without invoking adbi. For example:

library(adbcdrivermanager)

uri <-  "SECRET"
adbi_con <- adbc_database_init(adbcsnowflake::adbcsnowflake(), uri = uri) |> adbc_connection_init()

write_adbc(datasets::swiss, adbi_con, "SWISS")
read_adbc(adbi_con, "SELECT * from SWISS")

[klin333]

Hi,

The adbcdrivermanager code works fine

library(adbcdrivermanager)
uri <-  "SECRET"
adbi_con <- adbc_database_init(adbcsnowflake::adbcsnowflake(), uri = uri) |> adbc_connection_init()
write_adbc(datasets::swiss, adbi_con, "SWISS4")
read_adbc(adbi_con, "SELECT * from SWISS4")

<nanoarrow_array_stream struct<Fertility: double, Agriculture: double, Examination: decimal128(38, 0), Education: decimal128(38, 0), Catholic: double, Infant.Mortality: double>>
 $ get_schema:function ()  
 $ get_next  :function (schema = x$get_schema(), validate = TRUE)  
 $ release   :function ()  

However the adbi code still doesn't work

library(adbi)
uri <-  "SECRET"
adbi_con <- dbConnect(adbi::adbi("adbcsnowflake::adbcsnowflake"), uri = uri)
dbWriteTable(adbi_con, "SWISS3", datasets::swiss) # works
dbGetQuery(adbi_con, "SELECT * from SWISS3") # crashes

Error in adbcdrivermanager::adbc_statement_get_parameter_schema(stmt) : 
  NOT_IMPLEMENTED:

> sessionInfo()
R version 4.3.2 (2023-10-31 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 11 x64 (build 22631)

Matrix products: default


locale:
[1] LC_COLLATE=English_Australia.utf8  LC_CTYPE=English_Australia.utf8    LC_MONETARY=English_Australia.utf8
[4] LC_NUMERIC=C                       LC_TIME=English_Australia.utf8    

time zone: Australia/Sydney
tzcode source: internal

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] adbcdrivermanager_0.11.0 adbi_0.1.1              

loaded via a namespace (and not attached):
[1] compiler_4.3.2            DBI_1.2.2                 tools_4.3.2               rstudioapi_0.15.0        
[5] adbcsnowflake_0.11.0.9000 nanoarrow_0.4.0.1   

[paleolimbot]

The adbcdrivermanager code works fine

I think you may have to run as.data.frame(read_adbc(adbi_con, "SELECT * from SWISS4")) to get a fully equivalent operation (although it sounds like this is possibly just an unsupported operation that adbi is assuming works for all drivers).

[klin333]

yep, as.data.frame(read_adbc(adbi_con, "SELECT * from SWISS4")) works and returns expected data frame

[krlmlr]

Thanks.

@klin333: Can you please try

dbGetQuery(adbi_con, "SELECT * from SWISS3", immediate = TRUE)

?

@paleolimbot: Is there a way to tell if a particular ADBI driver supports parameterized queries, other than trying?

[paleolimbot]

Is there a way to tell if a particular ADBI driver supports parameterized queries, other than trying?

I don't think so. If this is critical, we could add the ability to return some R-level "capabilities" from the driver packages.

[lidavidm]

I wouldn't be opposed to adding actual bits to query that information to GetInfo

[paleolimbot]

It took me a second to remember where it was, but we do have a slightly out-of-date version of this in our documentation: https://arrow.apache.org/adbc/current/driver/status.html .

[krlmlr]

Thanks. I think DBI needs this info for queries to "just work".

[lidavidm]

Filed apache/arrow-adbc#1793

[klin333]

Thanks.

@klin333: Can you please try

dbGetQuery(adbi_con, "SELECT * from SWISS3", immediate = TRUE)

?

@paleolimbot: Is there a way to tell if a particular ADBI driver supports parameterized queries, other than trying?

Yep, dbGetQuery(adbi_con, "SELECT * from SWISS3", immediate = TRUE) works and returns expected data frame. This is similar to the hack fix I had in https://github.com/klin333/adbi/tree/fix_snowflake

[klin333]

hi everyone, really appreciate the help on this one. wonder if there's any ways to progress this issue? i understand that setting immediate = TRUE will fix the problem, but then the end goal is to use snowflake and adbi within dbplyr. thank you.