Failure to return SELECT query depending on params order

[grcatlin]

I am opening this issue here as I was unable to replicate with RSQLite + DBI so I assume the bug is either with the odbc package or with the ODBC Driver 18 for SQL Server driver.

In essence, DBI::dbGetQuery(conn, statement, params = params) can fail on a select statement using odbc::odbc() if, and seemingly only if, the very first row/element of params does not exist in the table. A full reprex using docker is below (though we have replicated using our production server):

docker pull mcr.microsoft.com/mssql/server:2022-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" -p 1433:1433 --name EXAMPLE  --hostname EXAMPLE -d mcr.microsoft.com/mssql/server:2022-latest

# connect
con <- DBI::dbConnect(
    drv = odbc::odbc(),
    driver = "ODBC Driver 18 for SQL Server",
    server = "0.0.0.0",
    UID = "sa",
    PWD = "<YourStrong@Passw0rd>",
    port = 1433,
    Encrypt = "no"
)

# example df
df <- data.frame(
    ID = stringi::stri_rand_strings(n = 100, length = 5)
)

# write to table
DBI::dbWriteTable(
    con,
    name = "ID",
    value = df,
    overwrite = TRUE
)

# sample id's
id <- sample(df$ID, size = 3)

# works as expected
DBI::dbGetQuery(
    con,
    "SELECT * FROM ID WHERE ID = ?",
    params = list(id)
)

     ID
1 zxadJ
2 wT9Fy
3 y9ZZt

# add nonexistent id's to the back
id <- c(id, "NOT_AN_ID")

# still works as expected
DBI::dbGetQuery(
    con,
    "SELECT * FROM ID WHERE ID = ?",
    params = list(id)
)

     ID
1 zxadJ
2 wT9Fy
3 y9ZZt

# add nonexistent id's to the front
id <- c("NOT_AN_ID", id)

# now it returns 0 rows
DBI::dbGetQuery(
    con,
    "SELECT * FROM ID WHERE ID = ?",
    params = list(id)
)

[1] ID
<0 rows> (or 0-length row.names)

I would expect that the resulting query to be the same in all 3 instances. If this is not the correct repo to open this issue in, please let me know the correct destination. Thanks!

R version 4.5.1 (2025-06-13)
Platform: aarch64-apple-darwin20
Running under: macOS Sequoia 15.5    

loaded via a namespace (and not attached):
 [1] bit_4.6.0       odbc_1.6.1      glue_1.8.0     
 [4] blob_1.2.4      pkgconfig_2.0.3 bit64_4.6.0-1  
 [7] lifecycle_1.0.4 cli_3.6.5       vctrs_0.6.5    
[10] DBI_1.2.3       compiler_4.5.1  tools_4.5.1    
[13] hms_1.1.3       pillar_1.10.2   Rcpp_1.0.14    
[16] rlang_1.1.6     jsonlite_2.0.0  fs_1.6.6       
[19] stringi_1.8.7  

[detule]

Thanks for the report, and the minimal example!
I can replicate, and have an idea what may be happening. Will try to fix, if possible, and report back here.