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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 >>>> <[EMAIL PROTECTED]> 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 >
