Re: Bug : FAST_NUMBER_FAILED when getting NaN on BigDecimal

On 09/12/2012 05:00 PM, DocSea - Patrice Delorme wrote:

> You are right, as a matter of Fact BigDecimal does not support NaN like
> Double, Float does.

Yep. There are some other areas where Java and PostgreSQL don't see eye
to eye that result in even more disturbing results. My "favourite" is
infinite dates, which PostgreSQL supports but Java does not. They're
currently just truncated to Java's biggest date. This means that
round-trips through Java can mangle data in new and exciting ways.
Particularly fun with ORMs that like to list every column, changed or
not, when doing an `UPDATE`.

> However, postgres DOES support it and I find it disturbing that the
> driver fails so badly without explaination (I had to dig in the driver
> source to find out what the problem was).

Yeah, that's an issue. How would you expect it to be handled?
Preferences/advice?

> The problem is that somehow I managed to write my Double NaN to database
> but wasn't able to read it back even though I was using the very same
> driver in both cases!

Yeah, that's not good. I'm honestly not sure what to do about it,
though. PgJDBC certainly can't return a Double NaN where it'd normally
return BigDecimal.

> To me, behaviour is not coherent. Either wriiting of NaN is prevented or
> reading is functional !

Agreed, that's nasty. The thing is that NaN *is* supported for Double,
so if your column type is float8 it'll all work fine.

To prevent Double (NaN) being sent as a value for a numeric column,
PgJDBC would need to ask the server for the column type and reject the
value only if the target column were numeric. Even then I'm not sure
that'd really do the job, as it'd be trivial to accidentally or
intentionally get around with functions/casts that take a double
argument and produce numeric.

> Maybe a more explicit Exception like "numeric NaN values not supported"
> and not "org.postgresql.util.PSQLException: Bad value for type
> BigDecimal : NaN;" which is rather obscure

Certainly some improvement to the message is warranted. I'm not sure I
like the proposed one though. Maybe "BigDecimal cannot represent NaN, so
the NUMERIC 'NaN' from PostgreSQL could not be returned." ?

> or to Extend BigDecimal with PgBigDecimal that supports NaN (and
> infinity)...

Tempting, but that way may lie madness given the assumptions code tends
to make about BigDecimal. I'd be pretty reluctant to do that, though it
might prove to be the best answer.

--
Craig Ringer