glue_sql struggles with Inf, -Inf, and NaN

[mmuurr]

Probably related (at least in spirit) to r-dbi/DBI#361.

Different DB back-ends will certainly have different behavior here, making this tricky. PostgreSQL (as the example with which I'm most familiar) supports 'Infinity', '-Infinity', and 'NaN' as numeric values (quotes necessary), as documented here. (Note that in earlier versions of PostgreSQL, the acceptable list of strings was smaller ... here's the documentation for v8.2 -- the oldest still-supported version).

With a PostgreSQL DBI connection object, here's how these values are converted:

glue::glue_sql("select x from y where z > {Inf}+ {-Inf} + {NaN}", .con = conn)

<SQL> select x from y where z > Inf + -Inf + NULL

... which will (syntax) err.

If adding some explicit type-casting to be a bit safer, we get:

glue::glue_sql("select x from y where z > {Inf}::float8 + {-Inf}::float8 + {NaN}::float8", .con = conn)

<SQL> select x from y where z > Inf::float8 + -Inf::float8 + NULL::float8

... which unfortunately will:

1. still (syntax) err on the infinite values, and
2. logically err on NULL value (as NULL math is different from NaN math, at least with PostgreSQL).

I'm not 100% sure what exactly should be done here, but I didn't see any issue mentioning this so minimally I figured I'd open this for discussion and future reference about this scenario. I believe this scenario arises in a fair number of instances, a common one being something like: "select x from y where {max(z)} > z" and z happens to be zero-length in the calling R code. Obviously the caller can try to conditionally rewrite that predicate, but it's pretty easy to see how this sneaks into code (and I'd argue having alternate SQL interpolations in application code for boundary conditions adds much cruff at the wrong level :-)).

[hadley]

I think this is probably out of scope for glue. glue_sql() is fundamentally about creating SQL by pasting strings together and there's only so much you can do at that level of abstraction.