r crash on dbBind

[r2evans]

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.)

[simonpcouch]

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}

[hadley]

I'd say we should fix this by automatically recycling the parameters to the correct length (or giving up with an error) in R.

[r2evans]

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

[hadley]

Yes, tidyverse recycling rules.

[r2evans]

(Yes, of course, duh ... I really prefer those over base R's recycling rules.)