db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Nielsen <Thomas.Niel...@Sun.COM>
Subject Re: Double.NaN in table?
Date Wed, 19 Dec 2007 09:23:32 GMT
Looking at the derby code on the main trunk, I see the exception is 
intentional, as there is an explicit check for NaN values in 
NumberDataType.normalizeDOUBLE().

Not sure if this is another DB2 compatability issue/limitation or not. 
Maybe someone with deeper knowledge can give more details on this?

Seems your only option at the moment is to add another column to your 
table to flag NaN values.

Thomas

Thomas Nielsen wrote:
> I quickly tried both of Andrews 1) and 2) suggestions. Results pasted 
> below.
> 
> Summary:
> - both PreparedStatement.setDouble() and setFloat() throw the same 
> Exception on respective .setXXX( index, XXX.NaN);
> - setFloat() has reference to DOUBLE range in exception message, which 
> would look strange to a user.
> 
> I haven't checked the JDBC spec, but I can't imagine storing Double.NaN 
> or Float.NaN should be illegal in JDBC. It may be implementation defined 
> though. How the db chooses to actually store the NaN values is another 
> discussion.
> 
> Cheers,
> Thomas
> 
> --- snip ---
> Getting connection ...
> Creating tables ...
> Inserting Double.NaN ...
> java.sql.SQLException: The resulting value is outside the range for the 
> data type DOUBLE.
>         at 
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)

> 
>         at 
> org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:202)
>         at 
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)

> 
>         at 
> org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(EmbedResultSet.java:4381)

> 
>         at 
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.setDouble(EmbedPreparedStatement.java:473)

> 
>         at TestNaN.test(TestNaN.java:60)
>         at TestNaN.test(TestNaN.java:30)
>         at TestNaN.main(TestNaN.java:25)
> Caused by: ERROR 22003: The resulting value is outside the range for the 
> data type DOUBLE.
>         at 
> org.apache.derby.iapi.error.StandardException.newException(StandardException.java:290)

> 
>         at 
> org.apache.derby.iapi.types.NumberDataType.normalizeDOUBLE(NumberDataType.java:539) 
> 
>         at 
> org.apache.derby.iapi.types.SQLDouble.setValue(SQLDouble.java:412)
>         at 
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.setDouble(EmbedPreparedStatement.java:470)

> 
>         ... 3 more
> Inserting Float.NaN ...
> java.sql.SQLException: The resulting value is outside the range for the 
> data type DOUBLE.
>         at 
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)

> 
>         at 
> org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:202)
>         at 
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)

> 
>         at 
> org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(EmbedResultSet.java:4381)

> 
>         at 
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.setFloat(EmbedPreparedStatement.java:452)

> 
>         at TestNaN.test(TestNaN.java:69)
>         at TestNaN.test(TestNaN.java:30)
>         at TestNaN.main(TestNaN.java:25)
> Caused by: ERROR 22003: The resulting value is outside the range for the 
> data type DOUBLE.
>         at 
> org.apache.derby.iapi.error.StandardException.newException(StandardException.java:290)

> 
>         at 
> org.apache.derby.iapi.types.NumberDataType.normalizeDOUBLE(NumberDataType.java:539) 
> 
>         at 
> org.apache.derby.iapi.types.SQLDouble.setValue(SQLDouble.java:421)
>         at 
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.setFloat(EmbedPreparedStatement.java:449)

> 
>         ... 3 more
> Closing connection ...
> --- snip ---
> 
> Andrew ``Bass'' Shcheglov wrote:
>> David,
>>
>>
>> could you please:
>>
>> 1. post a (partial, at least) stack trace along with a detail message 
>> you get;
>> 2. try a similar insertion of a Float.NaN into a FLOAT field and tell
>> what happens?
>>
>> String versions are only good if performance is really not an issue,
>> or record matching will be extremely slow.
>>
>> Otherwise, if your double values can be NaNs but are essentially not 
>> nullable,
>> you probably should use NULLs to represent NaNs at database level.
>>
>>
>> Regards,
>> Andrew.
> 

-- 
Thomas Nielsen

Mime
View raw message