Thread: Bug : FAST_NUMBER_FAILED when getting NaN on BigDecimal
Hi, It is impossible to fetch data when numeric value in database is NaN It throws FAST_NUMBER_FAILED (postgresql-jdbc-9.1-902:AbstractJdbc2ResultSet:2176) because my value is not numeric (NaN). This is incorect behaviour since 'NaN' is a legal value in postgres for numeric type. problem is in postgresql-jdbc-9.1-902.jdbc4 but it seems arround for ages... since I discovered it first in postgresql-8.3-603.jdbc3... best regards, P.
DocSea - Patrice Delorme <pdelorme@docsea.com> wrote: > It is impossible to fetch data when numeric value in database is > NaN > It throws FAST_NUMBER_FAILED > (postgresql-jdbc-9.1-902:AbstractJdbc2ResultSet:2176) because my > value is not numeric (NaN). > This is incorect behaviour since 'NaN' is a legal value in > postgres for numeric type. How do you expect what you read to be represented in Java? -Kevin
On 09/11/2012 01:37 PM, DocSea - Patrice Delorme wrote: > Hi, > > It is impossible to fetch data when numeric value in database is NaN > It throws FAST_NUMBER_FAILED (postgresql-jdbc-9.1-902:AbstractJdbc2ResultSet:2176) because my value is not numeric (NaN). > This is incorect behaviour since 'NaN' is a legal value in postgres for numeric type. > > problem is in postgresql-jdbc-9.1-902.jdbc4 > but it seems areound for ages... since I discovered it first in postgresql-8.3-603.jdbc3... Can you provide a more concrete example code to show when the FAST_NUMBER_FAILED exception is seen by jdbc using application. I did a quick look at the code and the exception in question looks like just a jdbc driver internal way to switch to slowerparsing of input values when fast parsing fails. It should never escape to applications. -Mikko
On Tue, Sep 11, 2012 at 4:10 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > DocSea - Patrice Delorme <pdelorme@docsea.com> wrote: > >> It is impossible to fetch data when numeric value in database is >> NaN >> It throws FAST_NUMBER_FAILED >> (postgresql-jdbc-9.1-902:AbstractJdbc2ResultSet:2176) because my >> value is not numeric (NaN). >> This is incorect behaviour since 'NaN' is a legal value in >> postgres for numeric type. > > How do you expect what you read to be represented in Java? java.lang.Double.NaN I guess. -- Florent Guillaume, Director of R&D, Nuxeo Open Source, Java EE based, Enterprise Content Management (ECM) http://www.nuxeo.com http://www.nuxeo.org +33 1 40 33 79 87
> -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc- > owner@postgresql.org] On Behalf Of Florent Guillaume > Sent: Wednesday, September 12, 2012 9:36 AM > To: Kevin Grittner > Cc: DocSea - Patrice Delorme; pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] Bug : FAST_NUMBER_FAILED when getting NaN on > BigDecimal > > On Tue, Sep 11, 2012 at 4:10 PM, Kevin Grittner > <Kevin.Grittner@wicourts.gov> wrote: > > DocSea - Patrice Delorme <pdelorme@docsea.com> wrote: > > > >> It is impossible to fetch data when numeric value in database is NaN > >> It throws FAST_NUMBER_FAILED > >> (postgresql-jdbc-9.1-902:AbstractJdbc2ResultSet:2176) because my > >> value is not numeric (NaN). > >> This is incorect behaviour since 'NaN' is a legal value in postgres > >> for numeric type. > > > > How do you expect what you read to be represented in Java? > > java.lang.Double.NaN I guess. > > I would think returning a special "Double" value when the expected datatype is "BigDecimal" would cause problems. I wouldexpect it to return NULL since that is the closest you are going to get to the semantic meaning of NaN with the existingBigDecimal class. Maybe someone would a add a class to the JDBC driver that is a sub-class of BigDecimal with infinity and NaN query/representationfeatures but since BigDecimal does not have them, and for backwards compatibility number-to-BigDecimalhas to remain, there really isn't any better option. Well, there is one. If the driver returns NULL is these situations it could still maintain internally whether the NULL isthe result of a conversion of INF/-INF/NaN or whether the NULL was in the data itself. The driver could expose a "isNaN(),isPosInf(), and isNegInf() methods that the user could query upon seeing a NULL BigDecimal so that it knows the"meaning" of the NULL. Backward compatibility only requires that the BigDecimal is still returned, new features can beadded. You either need to cast to a "float" in PostgreSQL so that the result is stored into a "Double" in Java or figure out someother application specific workaround. David J.
Le 11/09/2012 16:10, Kevin Grittner a écrit : > DocSea - Patrice Delorme <pdelorme@docsea.com> wrote: > >> It is impossible to fetch data when numeric value in database is >> NaN >> It throws FAST_NUMBER_FAILED >> (postgresql-jdbc-9.1-902:AbstractJdbc2ResultSet:2176) because my >> value is not numeric (NaN). >> This is incorect behaviour since 'NaN' is a legal value in >> postgres for numeric type. > > How do you expect what you read to be represented in Java? > > -Kevin > > Hi Kevin, You are right, as a matter of Fact BigDecimal does not support NaN like Double, Float does. 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). 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! To me, behaviour is not coherent. Either wriiting of NaN is prevented or reading is functional ! 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 or to Extend BigDecimal with PgBigDecimal that supports NaN (and infinity)... Best Regards, P.
David Johnston wrote: >>>> It is impossible to fetch data when numeric value in database is NaN >>> How do you expect what you read to be represented in Java? >> java.lang.Double.NaN I guess. > If the driver returns NULL is these situations it could still maintain internally > whether the NULL is the result of a conversion of INF/-INF/NaN or whether the NULL was in the data > itself. The driver could expose a "isNaN(), isPosInf(), and isNegInf() methods that the user could > query upon seeing a NULL BigDecimal so that it knows the "meaning" of the NULL. Backward > compatibility only requires that the BigDecimal is still returned, new features can be added. I don't think that's a good idea. Apart from the fact that a null pointer isn't a good representation for an out-of-bounds value, that would change the behaviour and might break applications that expect the current behaviour. I think it is OK to throw an exception if a value cannot be represented, but it should be a meaningful message. Yours, Laurenz Albe
> > You are right, as a matter of Fact BigDecimal does not support NaN like > Double, Float does. > 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). > 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! > To me, behaviour is not coherent. Either wriiting of NaN is prevented or > reading is functional ! > > 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 or to Extend BigDecimal with PgBigDecimal that > supports NaN (and infinity)... > While I can understand the frustration the behavior is such because in the vast majority of cases storing a double into a Postgres numeric is a reasonable thing to do (and, from the driver's standpoint, maybe impossible to detect with certainty anyway). The driver assuming the user of BigDecimal knows it cannot handle NaN is also not a bad assumption to make. Wording could maybe be improved but it susiscently reports the problem encountered (the database sent NaN and the BigDecimal had no idea what to do with it. It isn't like the driver has to know that NaN means Not-A-Number. If the database sent along 'Pig' the same error would be generated but with 'Pig' instead of NaN. Implicit conversions can and do cause problems. That is what happened here. As a user you should not have tried storing a Java Double into a Postgres numeric but you should have converted it into a BigDecimal yourself. If you want consistent behavior that is exactly what you would have been forced to do anyway. Of course you or the driver could write a wrapper do that and also check and report meaningful exceptions when the data and the target type do not match - but given the open source nature of the driver and the uncommon situation you presented, it is understandable that the behavior you saw manifested itself. No one else with the problem felt it was important enough to fix. David J.
> -----Original Message----- > From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] > Sent: Wednesday, September 12, 2012 10:48 AM > To: David Johnston *EXTERN*; Florent Guillaume; Kevin Grittner > Cc: DocSea - Patrice Delorme; pgsql-jdbc@postgresql.org > Subject: RE: [JDBC] Bug : FAST_NUMBER_FAILED when getting NaN on > BigDecimal > > David Johnston wrote: > >>>> It is impossible to fetch data when numeric value in database is > >>>> NaN > > >>> How do you expect what you read to be represented in Java? > > >> java.lang.Double.NaN I guess. > > > If the driver returns NULL is these situations it could still maintain > > internally whether the NULL is the result of a conversion of > > INF/-INF/NaN or whether the NULL was in the data itself. The driver > > could expose a "isNaN(), isPosInf(), and isNegInf() methods that the > > user could query upon seeing a NULL BigDecimal so that it knows the > "meaning" of the NULL. Backward compatibility only requires that the > BigDecimal is still returned, new features can be added. > > I don't think that's a good idea. > > Apart from the fact that a null pointer isn't a good representation for an out- > of-bounds value, that would change the behaviour and might break > applications that expect the current behaviour. > > I think it is OK to throw an exception if a value cannot be represented, but it > should be a meaningful message. > OK. So the API would be more along the lines of: "if you are concerned with NaN/Infinity values you can poll the resultfirst to see if the returned value is valid or not. If the returned value is invalid and the user tries to read thevalue they get their exception. If the user sees that the value is invalid (from the polling) then they should foregoreading the value and instead interpret whatever invalid value is indicated. An actual NULL would be a valid value." I am unsure exactly WHEN the exception in this case is thrown but if too early then sub-classing BigDecimal is not possiblesince the exception would preclude the object ever being created (for backwards compatibility). Personally, I believe there needs to be some way to access this aspect of the PostgreSQL numeric type but it is not somethingthat, aside from a desire for completeness, currently affects me. Even a more fine-grained exception hierarchy(NaNException, PositiveInfinityException, NegativeInfinityException) that the user could catch on could be helpful(instead of having to perform string manipulation of the error message). In any cases a non-standard class wouldbe necessary so whether the driver provides ones or it simply provide information and requires the user to code whateverClass is appropriate is an explicit decision to make. David J.
Hello, All solutions seem to have a really negative side. My opinion is: Maintain default old behaviour (avoid break things ...) Create url property to enable new behavior (big_decimal_nan=true) Create new PgBigDecimal class extending BigDecimal, with constants for NaN, NEGATIVE_INFINITY and POSITIVE_INFINITY (these values can be created with new BigDecimal( BigInteger.valueOf( 0 ), 100 ), new BigDecimal( BigInteger.valueOf( 0 ), 101 ), new BigDecimal( BigInteger.valueOf( 0 ), 102 ) ) Override most BigDecimal's method to throw Exception or give correct results on operations with these constants and converting values from/to Double. For those worried about using 0^X for these constants, this operation new BigDecimal( BigInteger.valueOf( 0 ), 100 ).equals( new BigDecimal( 0 ) ) returns false, which is a good thing :) I believe this is the only correct manner to use NaN with Numeric value. It's easy for the programmer. Doesn't get confused with NULL values. Doesn't broke software. Luis Flores On Wed, Sep 12, 2012 at 4:02 PM, David Johnston <polobo@yahoo.com> wrote: >> -----Original Message----- >> From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] >> Sent: Wednesday, September 12, 2012 10:48 AM >> To: David Johnston *EXTERN*; Florent Guillaume; Kevin Grittner >> Cc: DocSea - Patrice Delorme; pgsql-jdbc@postgresql.org >> Subject: RE: [JDBC] Bug : FAST_NUMBER_FAILED when getting NaN on >> BigDecimal >> >> David Johnston wrote: >> >>>> It is impossible to fetch data when numeric value in database is >> >>>> NaN >> >> >>> How do you expect what you read to be represented in Java? >> >> >> java.lang.Double.NaN I guess. >> >> > If the driver returns NULL is these situations it could still maintain >> > internally whether the NULL is the result of a conversion of >> > INF/-INF/NaN or whether the NULL was in the data itself. The driver >> > could expose a "isNaN(), isPosInf(), and isNegInf() methods that the >> > user could query upon seeing a NULL BigDecimal so that it knows the >> "meaning" of the NULL. Backward compatibility only requires that the >> BigDecimal is still returned, new features can be added. >> >> I don't think that's a good idea. >> >> Apart from the fact that a null pointer isn't a good representation for an out- >> of-bounds value, that would change the behaviour and might break >> applications that expect the current behaviour. >> >> I think it is OK to throw an exception if a value cannot be represented, but it >> should be a meaningful message. >> > > OK. So the API would be more along the lines of: "if you are concerned with NaN/Infinity values you can poll the resultfirst to see if the returned value is valid or not. If the returned value is invalid and the user tries to read thevalue they get their exception. If the user sees that the value is invalid (from the polling) then they should foregoreading the value and instead interpret whatever invalid value is indicated. An actual NULL would be a valid value." > > I am unsure exactly WHEN the exception in this case is thrown but if too early then sub-classing BigDecimal is not possiblesince the exception would preclude the object ever being created (for backwards compatibility). > > Personally, I believe there needs to be some way to access this aspect of the PostgreSQL numeric type but it is not somethingthat, aside from a desire for completeness, currently affects me. Even a more fine-grained exception hierarchy(NaNException, PositiveInfinityException, NegativeInfinityException) that the user could catch on could be helpful(instead of having to perform string manipulation of the error message). In any cases a non-standard class wouldbe necessary so whether the driver provides ones or it simply provide information and requires the user to code whateverClass is appropriate is an explicit decision to make. > > David J. > > > > > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
Patrice, Can you provide a test case which exhibits this behaviour. Preferably one which inserts the invalid data and can't read it back. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Wed, Sep 12, 2012 at 5:00 AM, DocSea - Patrice Delorme <pdelorme@docsea.com> wrote: > Le 11/09/2012 16:10, Kevin Grittner a écrit : > >> DocSea - Patrice Delorme <pdelorme@docsea.com> wrote: >> >>> >>> It is impossible to fetch data when numeric value in database is >>> NaN >>> It throws FAST_NUMBER_FAILED >>> (postgresql-jdbc-9.1-902:AbstractJdbc2ResultSet:2176) because my >>> value is not numeric (NaN). >>> This is incorect behaviour since 'NaN' is a legal value in >>> postgres for numeric type. >> >> How do you expect what you read to be represented in Java? >> -Kevin >> >> > Hi Kevin, > > You are right, as a matter of Fact BigDecimal does not support NaN like > Double, Float does. > 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). > 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! > To me, behaviour is not coherent. Either wriiting of NaN is prevented or > reading is functional ! > > 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 > or to Extend BigDecimal with PgBigDecimal that supports NaN (and > infinity)... > > Best Regards, > > P. > > > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
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
Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 09/12/2012 05:00 PM, DocSea - Patrice Delorme wrote:
>> 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." ?
For comparison, you can invoke the BigDecimal(double) constructor
with Double.NaN without any compile error, and at run time you get
this:
java.lang.NumberFormatException: Infinite or NaN
How differently do we want to treat this than the all-Java parallel
situation?
-Kevin