Infinite arguments require explicit type cast

[mmuurr]

Not sure what can be done about this, but if an SQL expression with parameters expects a float and Inf (or -Inf) is passed, PostgreSQL errs and complains like so:

dbGetQuery(conn, "select 0 + $1", Inf)
## Error: Failed to fetch row: ERROR:  invalid input syntax for type integer: "Inf"

With an explicit PostgreSQL type cast for the $1 parameter, however:

dbGetQuery(conn, "select 0 + $1::float", Inf)
## # A tibble: 1 x 1
##   `?column?`
##        <dbl>
## 1        Inf

This mirrors what happens in PostgreSQL:

select 0 + 'Infinity';  -- errs
select 0 + 'Infinity'::float;  -- succeeds

And so I don't think this counts as a bug, but it's sneaky in that a RPostgres user would have to protect her queries with type casts if there was any chance the R program would produce Inf as the passed value. Perhaps explicit casting should always be encouraged anyhow, though :-)

In any case, I don't know if this is known to y'all, and perhaps it's worth mentioning in docs (since PostgreSQL does support infinite values, but just requires this extra care in some situations to prevent errors).

[krlmlr]

Thanks. I think the recommendation makes sense for all databases. Can we document this in ?DBI::dbBind(), would you like to submit a pull request?

[mmuurr]

I guess this is ultimately the responsibility of each driver's dbQuoteLiteral() implementation? {RPostgres} already has conditionals based on object type, so adding tests for a few special values seems to make sense to me.

If that's a good way to go (as I've been bitten by this again recently, since min() and max() can return infinites) I can hop over to {RPostgres} and open a PR with a few such additional cases (and corresponding unit tests). For other back-ends I'm less useful, but obviously anyone can open driver-specific issues and use the Postgres solution as some sort of precedent.

Then the documentation add here would be a very small, "beware of special values -- they're handled (or not handled) by each driver differently" snippet.

[krlmlr]

I'd appreciate a tweak to RPostgres, thank you!