You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Thomas Nielsen (JIRA)" <ji...@apache.org> on 2007/12/19 14:10:43 UTC
[jira] Created: (DERBY-3290) Derby cannot store java Double or
Float values .NaN and .INFINITE
Derby cannot store java Double or Float values .NaN and .INFINITE
-----------------------------------------------------------------
Key: DERBY-3290
URL: https://issues.apache.org/jira/browse/DERBY-3290
Project: Derby
Issue Type: Improvement
Components: SQL
Reporter: Thomas Nielsen
Priority: Minor
Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3290) Derby cannot store java Double or
Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3290?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Nielsen updated DERBY-3290:
----------------------------------
Attachment: TestNaN.java
Attaching small repro java app using PreparedStatement.setDouble() and setFloat() in combination with NaN, POSITIVE_INFINITY, and NEGATIVE_INFINITY.
Sample call stack from insert attempt for 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:52)
at TestNaN.test(TestNaN.java:24)
at TestNaN.main(TestNaN.java:19)
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
---
> Derby cannot store java Double or Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
> -------------------------------------------------------------------------------------
>
> Key: DERBY-3290
> URL: https://issues.apache.org/jira/browse/DERBY-3290
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Thomas Nielsen
> Priority: Minor
> Attachments: TestNaN.java
>
>
> Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3290) Derby cannot store java Double or
Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12553403 ]
Daniel John Debrunner commented on DERBY-3290:
----------------------------------------------
Another issue would be backwards compatibility. Current Derby does perform the role of ensuring only valid numeric values are allowed in DOUBLE/REAL columns which (thanks to Knut Anders) does follow the SQL Standard.
Changing these datatypes to allow such values would be a change in behaviour and a dangerous one for any application relying on the current behaviour.
> Derby cannot store java Double or Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
> -------------------------------------------------------------------------------------
>
> Key: DERBY-3290
> URL: https://issues.apache.org/jira/browse/DERBY-3290
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Thomas Nielsen
> Priority: Minor
> Attachments: TestNaN.java
>
>
> Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3290) Derby cannot store java Double or
Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3290?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Thomas Nielsen updated DERBY-3290:
----------------------------------
Summary: Derby cannot store java Double or Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY (was: Derby cannot store java Double or Float values .NaN and .INFINITE)
> Derby cannot store java Double or Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
> -------------------------------------------------------------------------------------
>
> Key: DERBY-3290
> URL: https://issues.apache.org/jira/browse/DERBY-3290
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Thomas Nielsen
> Priority: Minor
>
> Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3290) Derby cannot store java Double or
Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12553366 ]
Thomas Nielsen commented on DERBY-3290:
---------------------------------------
Let's not get too philosophical - NaN is still a legal value for the numeric data type used in the java application, and users may want to store that for whatever reason(s) they have. As of today they have to work around this by adding extra columns (or otherwise) to flag NaN and/or infinite values.
Like you say Chapter 4.4.2 of [3] does indeed limit what is allowed as a REAL, FLOAT and DOUBLE PRECISION value, and could also be the reason for the lack of support in many (most?) databases for NaN and infinity numbers.
Proper support for IEEE 754 floating point numbers (even if it's borderline with the SQL spec) would still be very welcome to a lot of university/scientifc derby users IMHO.
> Derby cannot store java Double or Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
> -------------------------------------------------------------------------------------
>
> Key: DERBY-3290
> URL: https://issues.apache.org/jira/browse/DERBY-3290
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Thomas Nielsen
> Priority: Minor
> Attachments: TestNaN.java
>
>
> Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3290) Derby cannot store java Double or
Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12553363 ]
Knut Anders Hatlen commented on DERBY-3290:
-------------------------------------------
Not sure how NaN would be affected by maximum and minimum numbers
supported, since it's not a number... ;)
The syntax rules you mentioned in chapter 6.1 say that FLOAT, REAL and
DOUBLE PRECISION are approximate numeric. Chapter 4.4.2 says that "[an]
approximate numeric value consists of a mantissa and an exponent. The
mantissa is a signed numeric value, and the exponent is a signed integer
that specifies the magnitude of the mantissa. (...) The value of an
approximate numeric value is the mantissa multiplied by a factor
determined by the exponent." I'm not sure this allows representation of
NaN or infinity.
> Derby cannot store java Double or Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
> -------------------------------------------------------------------------------------
>
> Key: DERBY-3290
> URL: https://issues.apache.org/jira/browse/DERBY-3290
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Thomas Nielsen
> Priority: Minor
> Attachments: TestNaN.java
>
>
> Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Issue Comment Edited: (DERBY-3290) Derby cannot store java
Double or Float values .NaN and .INFINITE
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12553349 ]
thomanie edited comment on DERBY-3290 at 12/19/07 5:18 AM:
-----------------------------------------------------------------
Neither the JDBC 4.0 specification[1] nor "JDBC API Tutorial and Reference"[2] mention handling of the NaN or [POSITIVE|NEGATIVE]_INFINITY 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).
Continuing into the SQL 2003 foundation spec[3] I can't find any explicit mention of NaN/INFINITY 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.
In the end it boils down to what the database actually supports, and at the moment Derby does not support neither NaN or [POSITIVE|NEGATIVE]_INFINITY.
Given that Derby is a 100% java database it would seem appropriate that the legal java numbers NaN and [POSITIVE|NEGATIVE]_INFINITY should be supported for the java types Double and Float.
[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.
[3] ISO/IEC 9075-2:2003 (E), "Information technology - database languages - SQL - Part 2: Foundation (SQL/Foundation)"
was (Author: thomanie):
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).
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.
In the end it boils down to what the database actually 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.
[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.
[3] ISO/IEC 9075-2:2003 (E), "Information technology - database languages - SQL - Part 2: Foundation (SQL/Foundation)"
> Derby cannot store java Double or Float values .NaN and .INFINITE
> -----------------------------------------------------------------
>
> Key: DERBY-3290
> URL: https://issues.apache.org/jira/browse/DERBY-3290
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Thomas Nielsen
> Priority: Minor
>
> Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3290) Derby cannot store java Double or
Float values .NaN and .INFINITE
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12553349 ]
Thomas Nielsen commented on DERBY-3290:
---------------------------------------
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).
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.
In the end it boils down to what the database actually 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.
[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.
[3] ISO/IEC 9075-2:2003 (E), "Information technology - database languages - SQL - Part 2: Foundation (SQL/Foundation)"
> Derby cannot store java Double or Float values .NaN and .INFINITE
> -----------------------------------------------------------------
>
> Key: DERBY-3290
> URL: https://issues.apache.org/jira/browse/DERBY-3290
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Thomas Nielsen
> Priority: Minor
>
> Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3290) Derby cannot store java Double or
Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12553393 ]
Daniel John Debrunner commented on DERBY-3290:
----------------------------------------------
Just to add this is more involved than just removing the checks for Nan/Infinity. It needs to be thought out otherwise one can end up with a whole host of bugs (as I think the user thread pointed out for older versions of SQL Server). Examples of areas that require thought/work are:
- define how =,<,> work (most likely based upon IEEE)
- define how +,-,*,/ work (most likely based upon IEEE)
- does the decisions for <,> make sense for an index, where would Nan be ordered in an index?
- probably need to add builtin SQL functions for Double.isNan, isInfinite
- how do aggregates work
- does max() return positive infiinty if the column has that value, or does it throw an exception or ignore the value
- sum() with infinities, sum() with Nan - what happens ...
- etc.
- are literals needed for these values?
- conversion to other numeric types
My vague memory is that these values are not accepted in Derby to avoid all of these issues.
I'm not sure about the argument that because Java supports these values the database must, as a counter example Java supports unicode strings but it would
be valid for a database to only support a character set that is a subset of unicode, thus not supporting all of the character values that are valid in java.
> Derby cannot store java Double or Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
> -------------------------------------------------------------------------------------
>
> Key: DERBY-3290
> URL: https://issues.apache.org/jira/browse/DERBY-3290
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Thomas Nielsen
> Priority: Minor
> Attachments: TestNaN.java
>
>
> Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3290) Derby cannot store java Double or
Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
Posted by "Thomas Nielsen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12553410 ]
Thomas Nielsen commented on DERBY-3290:
---------------------------------------
Yes, it is definately more complex and involved than just removing the checks in NumberDataType.normalizeDOUBLE(). That is simply the codeline where Derby enforces this.
All of these considerations/questions, and possibly more, may be (one of) the reason(s) behind the SQL standard not touching on this at all but limits SQLs scope to approximate numeric.
> Derby cannot store java Double or Float values .NaN and .[POSITIVE|NEGATIVE]_INFINITY
> -------------------------------------------------------------------------------------
>
> Key: DERBY-3290
> URL: https://issues.apache.org/jira/browse/DERBY-3290
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Thomas Nielsen
> Priority: Minor
> Attachments: TestNaN.java
>
>
> Issue originally reported on derby-user, where user cannot store .NaN or .INFINITE for java datatypes Double or Float.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.