odbc
odbc copied to clipboard
r-dbi
r crash on dbBind
dbBind crashes (hard) when given multiple objects and one includes a cast from string to datetimeoffset (on mssql).
Up front, the failing code is:
res <- DBI::dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
DBI::dbBind(res, list(1003:1004, '2022-04-01 16:00:02.000000 +00:00'))
# Process R:1 exited abnormally with code 5 at Tue Apr 19 12:51:34 2022
The issue is triggered when one of the ?-param is length greater than 1 and another ?-param is a string that should be cast to datetimeoffset. If either of those is not true (all params are length 1, or the second param is POSIXt), it does not crash.
Reprex:
# con <- DBI::dbConnect(...)
DBI::dbGetQuery(con, "select @@version")[[1]]
# [1] "Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) \n\tMar 18 2018 09:11:49 \n\tCopyright (c) Microsoft Corporation\n\tStandard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )\n"
sapply(c("DBI", "odbc"), packageVersion)
# $DBI
# [1] 1 1 2
# $odbc
# [1] 1 3 3
dat <- data.frame(id = 1000:1004, timestamp = as.POSIXct("2022-04-01 12:00:00") + 1:5)
dat
# id timestamp
# 1 1000 2022-04-01 12:00:01
# 2 1001 2022-04-01 12:00:02
# 3 1002 2022-04-01 12:00:03
# 4 1003 2022-04-01 12:00:04
# 5 1004 2022-04-01 12:00:05
DBI::dbWriteTable(con, "#temptable2", dat, field.types = c(id="int", timestamp="datetimeoffset"))
DBI::dbGetQuery(con, "select * from #temptable2 where id=1004")
# id timestamp
# 1 1004 2022-04-01 16:00:05.0000000 +00:00
res <- DBI::dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
DBI::dbBind(res, list(1004L, dat$timestamp[2]))
DBI::dbFetch(res)
# id timestamp
# 1 1004 2022-04-01 16:00:05.0000000 +00:00
DBI::dbClearResult(res)
res <- DBI::dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
DBI::dbBind(res, list(1004L, '2022-04-01 16:00:02.000000 +00:00'))
DBI::dbFetch(res)
# id timestamp
# 1 1004 2022-04-01 16:00:05.0000000 +00:00
DBI::dbClearResult(res)
res <- DBI::dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
DBI::dbBind(res, list(1003:1004, dat$timestamp[2]))
DBI::dbFetch(res)
# id timestamp
# 1 1003 2022-04-01 16:00:04.0000000 +00:00
# 2 1004 2022-04-01 16:00:05.0000000 +00:00
DBI::dbClearResult(res)
res <- DBI::dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
DBI::dbBind(res, list(1003:1004, '2022-04-01 16:00:02.000000 +00:00'))
# Process R:1 exited abnormally with code 5 at Tue Apr 19 12:51:34 2022
This is R-4.1.2 on Win11. Using "Microsoft ODBC Driver 17 for SQL Server" version 17.8.1.1 (2021-12-28). (I don't think it's related, but I'm using emacs/ESS.)
Thanks for the thorough description. With my local setup—slightly newer versions all around on macOS aarch64, also using Microsoft's driver—seeing no crash but an error:
library(DBI)
library(odbc)
con <- dbConnect(odbc(), dsn = "MicrosoftSQLServer", uid = "SA",
pwd = Sys.getenv("sqlServerPass"))
dbGetQuery(con, "select @@version")[[1]]
#> [1] "Microsoft SQL Server 2022 (RTM-CU10) (KB5031778) - 16.0.4095.4 (X64) \n\tOct 30 2023 16:12:44 \n\tCopyright (C) 2022 Microsoft Corporation\n\tDeveloper Edition (64-bit) on Linux (Ubuntu 22.04.3 LTS) <X64>"
sapply(c("DBI", "odbc"), packageVersion)
#> $DBI
#> [1] 1 2 2
#>
#> $odbc
#> [1] 1 4 2
dat <- data.frame(id = 1000:1004, timestamp = as.POSIXct("2022-04-01 12:00:00") + 1:5)
dat
#> id timestamp
#> 1 1000 2022-04-01 12:00:01
#> 2 1001 2022-04-01 12:00:02
#> 3 1002 2022-04-01 12:00:03
#> 4 1003 2022-04-01 12:00:04
#> 5 1004 2022-04-01 12:00:05
dbWriteTable(con, "#temptable2", dat, field.types = c(id="int", timestamp="datetimeoffset"))
dbGetQuery(con, "select * from #temptable2 where id=1004")
#> id timestamp
#> 1 1004 2022-04-01 17:00:05.0000000 +00:00
res <- dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
dbBind(res, list(1004L, dat$timestamp[2]))
dbFetch(res)
#> id timestamp
#> 1 1004 2022-04-01 17:00:05.0000000 +00:00
dbClearResult(res)
res <- dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
dbBind(res, list(1004L, '2022-04-01 16:00:02.000000 +00:00'))
dbFetch(res)
#> id timestamp
#> 1 1004 2022-04-01 17:00:05.0000000 +00:00
dbClearResult(res)
res <- dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
dbBind(res, list(1003:1004, dat$timestamp[2]))
dbFetch(res)
#> id timestamp
#> 1 1003 2022-04-01 17:00:04.0000000 +00:00
#> 2 1004 2022-04-01 17:00:05.0000000 +00:00
dbClearResult(res)
res <- dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
dbBind(res, list(1003:1004, '2022-04-01 16:00:02.000000 +00:00'))
#> Error in result_bind(res@ptr, params, batch_rows): CHAR() can only be applied to a 'CHARSXP', not a 'NULL'
Created on 2024-03-12 with reprex v2.1.0
I'd say we should fix this by automatically recycling the parameters to the correct length (or giving up with an error) in R.
I'm assuming by "recycling" you mean "1 or n", not the "multiple-of" recycling that R sometimes allows? (I really hate that kind of recycling.)
BTW, this crashes on linux as well. Ubuntu-23.10, R-4.3.2, DBI-1.2.1, odbc-1.4.2, using MS's "18" driver (OP was "17").
> res <- DBI::dbSendStatement(con, "select * from #temptable2 where id = ? and timestamp > ?")
> DBI::dbBind(res, list(1003:1004, '2022-04-01 16:00:02.000000 +00:00'))
*** caught segfault ***
address 0x120000030, cause 'memory not mapped'
Traceback:
1: result_bind(res@ptr, params, batch_rows)
2: .local(res, params, ...)
3: DBI::dbBind(res, list(1003:1004, "2022-04-01 16:00:02.000000 +00:00"))
4: DBI::dbBind(res, list(1003:1004, "2022-04-01 16:00:02.000000 +00:00"))
Possible actions:
1: abort (with core dump, if enabled)
2: normal R exit
3: exit R without saving workspace
4: exit R saving workspace
(Yes, of course, duh ... I really prefer those over base R's recycling rules.)