dbQuoteLiteral() for numerics has insufficient precision in some cases

[krlmlr]

library(DBI)

data <- 0.0027392760273972603
sql <- dbQuoteLiteral(ANSI(), data)

sql
#> <SQL> 0.00273927602739726
as.numeric(sql) - data
#> [1] -4.336809e-19

sql_exp <- sprintf("%.17e", data)

sql_exp
#> [1] "2.73927602739726037e-03"
as.numeric(sql_exp) - data
#> [1] 0

^{Created on 2022-12-09 with reprex v2.0.2}

We could use sprintf("%.17e") as in the reprex to always convert to exponential notation with enough decimal numbers.

@hannes: Are you aware of databases that would complain about numbers passed in exponential notation in situations when they wouldn't complain about decimal numbers?

[krlmlr]

I'll give it a try, at least one resource suggests that exponential notation is okay: https://sql-academy.org/en/guide/literals#numeric-literals.

And another resource even suggests that this is the way to indicate floating-point literals.

[krlmlr]

I had to revert this because reverse dependencies were failing. Need a more robust way.