You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by David <dh...@TheSolutionWorks.com> on 2007/12/19 04:23:56 UTC
Double.NaN in table?
For an application I am writing I need to store doubles in a table. I
thought I could use the DOUBLE type when creating the columns in my tables.
Unfortunately the insert fails when the value of the double is Double.NaN.
We use NaN a lot in our analysis. Is there a way to make this work, or
should we just convert everything to a string?
We are using Java 6.x with JavaDB (Derby). Our tables are around 8,000 rows
(200,000 rows in the future) by 15 columns.
Performance is not much of an issue in this part of our system, we are only
using the database so that we can be flexible with our filtering of this
data.
Any ideas?
Thanks in advance,
-David
I am new to database programming.
Re: Double.NaN in table?
Posted by Mark Thornton <mt...@optrak.co.uk>.
Daniel John Debrunner wrote:
> David wrote:
>> For an application I am writing I need to store doubles in a table.
>> I thought I could use the DOUBLE type when creating the columns in my
>> tables. Unfortunately the insert fails when the value of the double
>> is Double.NaN. We use NaN a lot in our analysis. Is there a way to
>> make this work, or should we just convert everything to a string?
>
>>
>> Any ideas?
>
> One possible alternative is to use a BIGINT column.
>
> Use the java method Double.doubleToLongBits() when storing the value
> in the database, e.g.
>
> double myDouble = ...;
>
> ps.setLong(1, Double.doubleToLongBits(myDouble));
>
> and Double.longBitsToDouble() when reading values, e.g.
>
> double myDouble = Double.longBitsToDouble(rs.getLong(1));
>
> I haven't tried this, and you should probably read the descriptions
> for those methods, they have some detailed info on Nans. There is also
> the doubleToRawLongBits() method that could be used.
>
> No idea on how indexing on the BIGINT column would work with this
> approach, ie. if the index order would be useful.
>
> HTH,
> Dan.
>
It works in terms of storing the data, and I think the index order is
reasonable. However the semantic meaning of the field is lost from the
SQL perspective.
Mark Thornton
Re: Double.NaN in table?
Posted by Daniel John Debrunner <dj...@apache.org>.
David wrote:
> For an application I am writing I need to store doubles in a table. I
> thought I could use the DOUBLE type when creating the columns in my tables.
> Unfortunately the insert fails when the value of the double is Double.NaN.
> We use NaN a lot in our analysis. Is there a way to make this work, or
> should we just convert everything to a string?
>
> Any ideas?
One possible alternative is to use a BIGINT column.
Use the java method Double.doubleToLongBits() when storing the value in
the database, e.g.
double myDouble = ...;
ps.setLong(1, Double.doubleToLongBits(myDouble));
and Double.longBitsToDouble() when reading values, e.g.
double myDouble = Double.longBitsToDouble(rs.getLong(1));
I haven't tried this, and you should probably read the descriptions for
those methods, they have some detailed info on Nans. There is also the
doubleToRawLongBits() method that could be used.
No idea on how indexing on the BIGINT column would work with this
approach, ie. if the index order would be useful.
HTH,
Dan.
Re: Double.NaN in table?
Posted by Daniel John Debrunner <dj...@apache.org>.
Andrew ``Bass'' Shcheglov wrote:
> On Dec 19, 2007 12:23 PM, Thomas Nielsen <Th...@sun.com> wrote:
>> 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?
>
>
> This is odd,
>
> for Oracle, for instance, fully supports IEEE 754 (since 10g):
>
> http://www.oracle.com/technology/sample_code/tutorials/jdbc10g/ieeedatatypes/files/IEEEDatatypes.pdf
Note that's Oracle specific and non-standard, requires:
- a Oracle specific SQL data type BINARY_DOUBLE or BINARY_FLOAT
- requires use of the Oracle specific
setBinaryDouble()/setBinaryFloat() after casting the PreparedStatement
down to an OraclePreparedStatement.
Dan.
Re: Double.NaN in table?
Posted by Andrew ``Bass'' Shcheglov <an...@gmail.com>.
On Dec 19, 2007 4:30 PM, Thomas Nielsen <Th...@sun.com> wrote:
> I filed handling NaN, POSITIVE_INFINITY and NEGATIVE_INFINITY as an
> improvement suggestion in jira:
> https://issues.apache.org/jira/browse/DERBY-3290
Thanks Thomas.
--
Andrew ``Bass'' Shcheglov
http://www.2ka.mipt.ru/~bass/
http://www.softlogic.ru/
Re: Double.NaN in table?
Posted by David <dh...@TheSolutionWorks.com>.
Thank you very much to all who have replied. The insight is very
interesting, and the suggestions are helpful. Since performance is not an
issue, I will just use one of the work-arounds.
Thanks,
David
"Thomas Nielsen" <Th...@Sun.COM> wrote in
message news:47691CE9.1000800@sun.com...
> FYI:
>
> I filed handling NaN, POSITIVE_INFINITY and NEGATIVE_INFINITY as an
> improvement suggestion in jira:
> https://issues.apache.org/jira/browse/DERBY-3290
>
> BR,
> Thomas
>
> Thomas Nielsen wrote:
>> Continuing into the SQL 2003 foundation spec[3] I can't find any explicit
>> mention of NaN/INFINITE numbers for either FLOAT, REAL or DOUBLE
>> PRECISION sql datatypes.
>>
>> The combined information from
>> - chapter 4.4.1 "Characteristics of numbers", and
>> - chapter 6.1 "Data Types", section "Syntax Rules", paragraphs 24)
>> through 27),
>> state that the maximum and minimum numbers supported are implementation
>> defined for the sql datatypes REAL, FLOAT and DOUBLE PRECISION.
>>
>> So, yes, it boils down to what the database acutally supports, and at the
>> moment Derby does not support neither NaN or INFINITE.
>>
>> Given that Derby is a 100% java database it would seem appropriate that
>> the legal java numbers NaN and INFINITE should be supported for the java
>> types Double and Float.
>>
>> Cheers,
>> Thomas
>>
>> [3] ISO/IEC 9075-2:2003 (E), "Information technology - database
>> languages - SQL - Part 2: Foundation (SQL/Foundation)"
>>
>> Thomas Nielsen wrote:
>>> Neither the JDBC 4.0 specification[1] nor "JDBC API Tutorial and
>>> Reference"[2] mention handling of the NaN or INFINITE numbers at all
>>> AFAICT.
>>>
>>> As there is no mention of how to handle *jdbc* doubles (and literals),
>>> it seems this then boils down to how/if the database handle the *sql*
>>> double (and literals).
>>>
>>> Cheers,
>>> Thomas
>>>
>>> [1] JSR-221, JDBC 4.0,
>>> http://jcp.org/aboutJava/communityprocess/final/jsr221/index.html
>>>
>>> [2] "JDBC API Tutorial and Reference, Third Edt.", Fisher, Ellis &
>>> Bruce, Addison-Wesley 2003.
>>>
>>> Andrew ``Bass'' Shcheglov wrote:
>>>> On Dec 19, 2007 12:23 PM, Thomas Nielsen
>>>> <Th...@sun.com> wrote:
>>>>> 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?
>>>>
>>>>
>>>> This is odd,
>>>>
>>>> for Oracle, for instance, fully supports IEEE 754 (since 10g):
>>>>
>>>> http://www.oracle.com/technology/sample_code/tutorials/jdbc10g/ieeedatatypes/files/IEEEDatatypes.pdf
>>>> http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements003.htm
>>>
>>
>
> --
> Thomas Nielsen
>
Re: Double.NaN in table?
Posted by Thomas Nielsen <Th...@Sun.COM>.
FYI:
I filed handling NaN, POSITIVE_INFINITY and NEGATIVE_INFINITY as an
improvement suggestion in jira:
https://issues.apache.org/jira/browse/DERBY-3290
BR,
Thomas
Thomas Nielsen wrote:
> Continuing into the SQL 2003 foundation spec[3] I can't find any
> explicit mention of NaN/INFINITE numbers for either FLOAT, REAL or
> DOUBLE PRECISION sql datatypes.
>
> The combined information from
> - chapter 4.4.1 "Characteristics of numbers", and
> - chapter 6.1 "Data Types", section "Syntax Rules", paragraphs 24)
> through 27),
> state that the maximum and minimum numbers supported are implementation
> defined for the sql datatypes REAL, FLOAT and DOUBLE PRECISION.
>
> So, yes, it boils down to what the database acutally supports, and at
> the moment Derby does not support neither NaN or INFINITE.
>
> Given that Derby is a 100% java database it would seem appropriate that
> the legal java numbers NaN and INFINITE should be supported for the java
> types Double and Float.
>
> Cheers,
> Thomas
>
> [3] ISO/IEC 9075-2:2003 (E), "Information technology - database
> languages - SQL - Part 2: Foundation (SQL/Foundation)"
>
> Thomas Nielsen wrote:
>> Neither the JDBC 4.0 specification[1] nor "JDBC API Tutorial and
>> Reference"[2] mention handling of the NaN or INFINITE numbers at all
>> AFAICT.
>>
>> As there is no mention of how to handle *jdbc* doubles (and literals),
>> it seems this then boils down to how/if the database handle the *sql*
>> double (and literals).
>>
>> Cheers,
>> Thomas
>>
>> [1] JSR-221, JDBC 4.0,
>> http://jcp.org/aboutJava/communityprocess/final/jsr221/index.html
>>
>> [2] "JDBC API Tutorial and Reference, Third Edt.", Fisher, Ellis &
>> Bruce, Addison-Wesley 2003.
>>
>> Andrew ``Bass'' Shcheglov wrote:
>>> On Dec 19, 2007 12:23 PM, Thomas Nielsen <Th...@sun.com> wrote:
>>>> 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?
>>>
>>>
>>> This is odd,
>>>
>>> for Oracle, for instance, fully supports IEEE 754 (since 10g):
>>>
>>> http://www.oracle.com/technology/sample_code/tutorials/jdbc10g/ieeedatatypes/files/IEEEDatatypes.pdf
>>>
>>> http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements003.htm
>>>
>>
>
--
Thomas Nielsen
Re: Double.NaN in table?
Posted by Thomas Nielsen <Th...@Sun.COM>.
Continuing into the SQL 2003 foundation spec[3] I can't find any
explicit mention of NaN/INFINITE numbers for either FLOAT, REAL or
DOUBLE PRECISION sql datatypes.
The combined information from
- chapter 4.4.1 "Characteristics of numbers", and
- chapter 6.1 "Data Types", section "Syntax Rules", paragraphs 24)
through 27),
state that the maximum and minimum numbers supported are implementation
defined for the sql datatypes REAL, FLOAT and DOUBLE PRECISION.
So, yes, it boils down to what the database acutally supports, and at
the moment Derby does not support neither NaN or INFINITE.
Given that Derby is a 100% java database it would seem appropriate that
the legal java numbers NaN and INFINITE should be supported for the java
types Double and Float.
Cheers,
Thomas
[3] ISO/IEC 9075-2:2003 (E), "Information technology - database
languages - SQL - Part 2: Foundation (SQL/Foundation)"
Thomas Nielsen wrote:
> Neither the JDBC 4.0 specification[1] nor "JDBC API Tutorial and
> Reference"[2] mention handling of the NaN or INFINITE numbers at all
> AFAICT.
>
> As there is no mention of how to handle *jdbc* doubles (and literals),
> it seems this then boils down to how/if the database handle the *sql*
> double (and literals).
>
> Cheers,
> Thomas
>
> [1] JSR-221, JDBC 4.0,
> http://jcp.org/aboutJava/communityprocess/final/jsr221/index.html
>
> [2] "JDBC API Tutorial and Reference, Third Edt.", Fisher, Ellis &
> Bruce, Addison-Wesley 2003.
>
> Andrew ``Bass'' Shcheglov wrote:
>> On Dec 19, 2007 12:23 PM, Thomas Nielsen <Th...@sun.com> wrote:
>>> 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?
>>
>>
>> This is odd,
>>
>> for Oracle, for instance, fully supports IEEE 754 (since 10g):
>>
>> http://www.oracle.com/technology/sample_code/tutorials/jdbc10g/ieeedatatypes/files/IEEEDatatypes.pdf
>>
>> http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements003.htm
>>
>
--
Thomas Nielsen
Re: Double.NaN in table?
Posted by Thomas Nielsen <Th...@Sun.COM>.
Neither the JDBC 4.0 specification[1] nor "JDBC API Tutorial and
Reference"[2] mention handling of the NaN or INFINITE numbers at all AFAICT.
As there is no mention of how to handle *jdbc* doubles (and literals),
it seems this then boils down to how/if the database handle the *sql*
double (and literals).
Cheers,
Thomas
[1] JSR-221, JDBC 4.0,
http://jcp.org/aboutJava/communityprocess/final/jsr221/index.html
[2] "JDBC API Tutorial and Reference, Third Edt.", Fisher, Ellis &
Bruce, Addison-Wesley 2003.
Andrew ``Bass'' Shcheglov wrote:
> On Dec 19, 2007 12:23 PM, Thomas Nielsen <Th...@sun.com> wrote:
>> 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?
>
>
> This is odd,
>
> for Oracle, for instance, fully supports IEEE 754 (since 10g):
>
> http://www.oracle.com/technology/sample_code/tutorials/jdbc10g/ieeedatatypes/files/IEEEDatatypes.pdf
> http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements003.htm
--
Thomas Nielsen
Re: Double.NaN in table?
Posted by Mark Thornton <mt...@optrak.co.uk>.
Andrew ``Bass'' Shcheglov wrote:
> On Dec 19, 2007 12:23 PM, Thomas Nielsen <Th...@sun.com> wrote:
>
>> 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?
>>
>
>
> This is odd,
>
> for Oracle, for instance, fully supports IEEE 754 (since 10g):
>
> http://www.oracle.com/technology/sample_code/tutorials/jdbc10g/ieeedatatypes/files/IEEEDatatypes.pdf
> http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements003.htm
>
A few more data points. PostgreSQL supports it, but MySQL does not (they
claim it is not supported in the SQL standard).
SqlServer 2000 did allow NaN but with a host of bugs if you tried, SQL
Server 2005 checks for NaN and rejects it. As far as I can remember
versions of SQL Server prior to 2000 did not allow NaN.
My SQL Standard handbook (1992) contains no mention of any of the
special floating point values. Odd given that IEEE arithmetic had been
around for quite a while by that date.
Mark Thornton
Re: Double.NaN in table?
Posted by Andrew ``Bass'' Shcheglov <an...@gmail.com>.
On Dec 19, 2007 12:23 PM, Thomas Nielsen <Th...@sun.com> wrote:
> 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?
This is odd,
for Oracle, for instance, fully supports IEEE 754 (since 10g):
http://www.oracle.com/technology/sample_code/tutorials/jdbc10g/ieeedatatypes/files/IEEEDatatypes.pdf
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elements003.htm
Re: Double.NaN in table?
Posted by Thomas Nielsen <Th...@Sun.COM>.
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
Re: Double.NaN in table?
Posted by Thomas Nielsen <Th...@Sun.COM>.
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
Re: Double.NaN in table?
Posted by Andrew ``Bass'' Shcheglov <an...@gmail.com>.
> Microsoft's SQL Server doesn't allow any of the special values in
> float/double fields. (i.e. positive/negative infinity or NaN). It is
> very annoying.
If +INF/-INF are to be stored along with NaNs,
and the database server (Derby in our case) doesn't allow it directly,
I don't see how one can accomplish this w/o introducing an extra column
(of type TINYINT, for instance) which would store a boolean flag
to tell you whether your double value is a regular or a special one.
--
Andrew ``Bass'' Shcheglov
http://www.2ka.mipt.ru/~bass/
http://www.softlogic.ru/
Re: Double.NaN in table?
Posted by Mark Thornton <mt...@optrak.co.uk>.
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.
>
Microsoft's SQL Server doesn't allow any of the special values in
float/double fields. (i.e. positive/negative infinity or NaN). It is
very annoying.
Mark Thornton
Re: Double.NaN in table?
Posted by Andrew ``Bass'' Shcheglov <an...@gmail.com>.
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.
Re: Double.NaN in table?
Posted by Suavi Ali Demir <de...@yahoo.com>.
By the way, while deciding whether a value x== Double.Nan or not, you probably cannot use == operator (may never be true). But if you compare string versions, they should equal ("NaN").
Ali
Suavi Ali Demir <de...@yahoo.com> wrote:
Can you make your column nullable and insert null instead of Nan?
Ali
David <dh...@TheSolutionWorks.com> wrote:
For an application I am writing I need to store doubles in a table. I
thought I could use the DOUBLE type when creating the columns in my tables.
Unfortunately the insert fails when the value of the double is Double.NaN.
We use NaN a lot in our analysis. Is there a way to make this work, or
should we just convert everything to a string?
We are using Java 6.x with JavaDB (Derby). Our tables are around 8,000 rows
(200,000 rows in the future) by 15 columns.
Performance is not much of an issue in this part of our system, we are only
using the database so that we can be flexible with our filtering of this
data.
Any ideas?
Thanks in advance,
-David
I am new to database programming.
---------------------------------
Never miss a thing. Make Yahoo your homepage.
---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
Re: Double.NaN in table?
Posted by Suavi Ali Demir <de...@yahoo.com>.
Can you make your column nullable and insert null instead of Nan?
Ali
David <dh...@TheSolutionWorks.com> wrote:
For an application I am writing I need to store doubles in a table. I
thought I could use the DOUBLE type when creating the columns in my tables.
Unfortunately the insert fails when the value of the double is Double.NaN.
We use NaN a lot in our analysis. Is there a way to make this work, or
should we just convert everything to a string?
We are using Java 6.x with JavaDB (Derby). Our tables are around 8,000 rows
(200,000 rows in the future) by 15 columns.
Performance is not much of an issue in this part of our system, we are only
using the database so that we can be flexible with our filtering of this
data.
Any ideas?
Thanks in advance,
-David
I am new to database programming.
---------------------------------
Never miss a thing. Make Yahoo your homepage.