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 Jim Newsham <jn...@referentia.com> on 2007/10/30 21:01:00 UTC
Is derby sql "real" equivalent to java "float"
I just saw a derby exception in our app, which seemed to have failed while
converting a Java float to a Derby REAL. at least, that's what our code
should be doing, unless we have some sort of generics leak (unsafe
conversion or something). I'll dig further to confirm that our code is
correct. Our codebase is pretty mature, and this is the first time I've seen
the error.
In the meantime, I'd like to ask. Should Java float always be safely
settable to a field of Derby type REAL? This has been my assumption all
along, and refderby.pdf seems to imply this. Or, are there edge cases, such
as perhaps NaN which will cause the set operation to fail.
Here is the offending line in our code. Presumably, a table which contains
a REAL field should have been selected by the same FLOAT enum value, so this
should be setting a REAL field.
case FLOAT: stmt.setFloat(column, (Float) value); break;
Here is the stack trace. It's too bad derby didn't include the offending
float value in the stack trace. The failure seems to be generated in
NumberDataType.normalizeREAL.
Caused by: java.sql.SQLDataException: The resulting value is outside the
range for the data type REAL.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknow
n Source)
at
org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown
Source)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.setFloat(Unknown Source)
at
com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.setParameter(Der
bySampleBase.java:576)
at
com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.access$200(Derby
SampleBase.java:71)
at
com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$1.perform(DerbyS
ampleBase.java:515)
at
com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransac
tion(DerbySampleBase.java:2755)
... 6 more
Caused by: java.sql.SQLException: The resulting value is outside the range
for the data type REAL.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossD
RDA(Unknown Source)
... 15 more
Caused by: ERROR 22003: The resulting value is outside the range for the
data type REAL.
at
org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.iapi.types.NumberDataType.normalizeREAL(Unknown
Source)
at org.apache.derby.iapi.types.SQLReal.setValue(Unknown Source)
... 11 more
Thanks,
Jim
Re: Is derby sql "real" equivalent to java "float"
Posted by Thomas Nielsen <Th...@Sun.COM>.
Dag H. Wanvik wrote:
> I think this code is old DB2 compatibility code, which could possibly be
> lifted now. Does anyone know?
IMHO this limitation should be lifted.
Thomas
--
Thomas Nielsen
Re: Is derby sql "real" equivalent to java "float"
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Given, Derby is an independent Apache product, i do not see a reason
keep this restriction.
Dag H. Wanvik wrote:
> Hi Jim,
>
> I found the following code in Derby, which may be the issue:
>
>
> normalizeREAL checks the validity of the given java float that
> it fits within the range of DB2 REALs. In addition it
> normalizes the value, so that negative zero (-0.0) becomes positive.
> */
> public static float normalizeREAL(float v) throws StandardException
> {
> if ( (Float.isNaN(v) || Float.isInfinite(v)) ||
> ((v < Limits.DB2_SMALLEST_REAL) || (v > Limits.DB2_LARGEST_REAL)) ||
> ((v > 0) && (v < Limits.DB2_SMALLEST_POSITIVE_REAL)) ||
> ((v < 0) && (v > Limits.DB2_LARGEST_NEGATIVE_REAL)) )
> {
> throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE,
> TypeId.REAL_NAME);
>
> The limits are declared as:
>
> static final float DB2_SMALLEST_REAL = -3.402E+38f;
> static final float DB2_LARGEST_REAL = +3.402E+38f;
> static final float DB2_SMALLEST_POSITIVE_REAL = +1.175E-37f;
> static final float DB2_LARGEST_NEGATIVE_REAL = -1.175E-37f;
>
> I think this code is old DB2 compatibility code, which could possibly be
> lifted now. Does anyone know?
>
> Thanks,
> Dag
>
>
>
> Jim Newsham <jn...@referentia.com> writes:
>
>
>>
>>
>> I just saw a derby exception in our app, which seemed to have failed while
>> converting a Java float to a Derby REAL. at least, that's what our code
>> should be doing, unless we have some sort of generics leak (unsafe
>> conversion or something). I'll dig further to confirm that our code is
>> correct. Our codebase is pretty mature, and this is the first time I've seen
>> the error.
>>
>>
>>
>> In the meantime, I'd like to ask. Should Java float always be safely
>> settable to a field of Derby type REAL? This has been my assumption all
>> along, and refderby.pdf seems to imply this. Or, are there edge cases, such
>> as perhaps NaN which will cause the set operation to fail.
>>
>>
>>
>> Here is the offending line in our code. Presumably, a table which contains
>> a REAL field should have been selected by the same FLOAT enum value, so this
>> should be setting a REAL field.
>>
>>
>>
>> case FLOAT: stmt.setFloat(column, (Float) value); break;
>>
>>
>>
>> Here is the stack trace. It's too bad derby didn't include the offending
>> float value in the stack trace. The failure seems to be generated in
>> NumberDataType.normalizeREAL.
>>
>>
>>
>> Caused by: java.sql.SQLDataException: The resulting value is outside the
>> range for the data type REAL.
>>
>> at
>> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
>> Source)
>>
>> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
>> Source)
>>
>> at
>> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknow
>> n Source)
>>
>> at
>> org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown
>> Source)
>>
>> at
>> org.apache.derby.impl.jdbc.EmbedPreparedStatement.setFloat(Unknown Source)
>>
>> at
>> com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.setParameter(Der
>> bySampleBase.java:576)
>>
>> at
>> com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.access$200(Derby
>> SampleBase.java:71)
>>
>> at
>> com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$1.perform(DerbyS
>> ampleBase.java:515)
>>
>> at
>> com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransac
>> tion(DerbySampleBase.java:2755)
>>
>> ... 6 more
>>
>> Caused by: java.sql.SQLException: The resulting value is outside the range
>> for the data type REAL.
>>
>> at
>> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
>> Source)
>>
>> at
>> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossD
>> RDA(Unknown Source)
>>
>> ... 15 more
>>
>> Caused by: ERROR 22003: The resulting value is outside the range for the
>> data type REAL.
>>
>> at
>> org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>>
>> at org.apache.derby.iapi.types.NumberDataType.normalizeREAL(Unknown
>> Source)
>>
>> at org.apache.derby.iapi.types.SQLReal.setValue(Unknown Source)
>>
>> ... 11 more
>>
>>
>>
>> Thanks,
>>
>> Jim
>>
>>
>
>
Re: Is derby sql "real" equivalent to java "float"
Posted by "Dag H. Wanvik" <hd...@Sun.COM>.
Hi Jim,
I found the following code in Derby, which may be the issue:
normalizeREAL checks the validity of the given java float that
it fits within the range of DB2 REALs. In addition it
normalizes the value, so that negative zero (-0.0) becomes positive.
*/
public static float normalizeREAL(float v) throws StandardException
{
if ( (Float.isNaN(v) || Float.isInfinite(v)) ||
((v < Limits.DB2_SMALLEST_REAL) || (v > Limits.DB2_LARGEST_REAL)) ||
((v > 0) && (v < Limits.DB2_SMALLEST_POSITIVE_REAL)) ||
((v < 0) && (v > Limits.DB2_LARGEST_NEGATIVE_REAL)) )
{
throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE,
TypeId.REAL_NAME);
The limits are declared as:
static final float DB2_SMALLEST_REAL = -3.402E+38f;
static final float DB2_LARGEST_REAL = +3.402E+38f;
static final float DB2_SMALLEST_POSITIVE_REAL = +1.175E-37f;
static final float DB2_LARGEST_NEGATIVE_REAL = -1.175E-37f;
I think this code is old DB2 compatibility code, which could possibly be
lifted now. Does anyone know?
Thanks,
Dag
Jim Newsham <jn...@referentia.com> writes:
>
>
> I just saw a derby exception in our app, which seemed to have failed while
> converting a Java float to a Derby REAL. at least, that's what our code
> should be doing, unless we have some sort of generics leak (unsafe
> conversion or something). I'll dig further to confirm that our code is
> correct. Our codebase is pretty mature, and this is the first time I've seen
> the error.
>
>
>
> In the meantime, I'd like to ask. Should Java float always be safely
> settable to a field of Derby type REAL? This has been my assumption all
> along, and refderby.pdf seems to imply this. Or, are there edge cases, such
> as perhaps NaN which will cause the set operation to fail.
>
>
>
> Here is the offending line in our code. Presumably, a table which contains
> a REAL field should have been selected by the same FLOAT enum value, so this
> should be setting a REAL field.
>
>
>
> case FLOAT: stmt.setFloat(column, (Float) value); break;
>
>
>
> Here is the stack trace. It's too bad derby didn't include the offending
> float value in the stack trace. The failure seems to be generated in
> NumberDataType.normalizeREAL.
>
>
>
> Caused by: java.sql.SQLDataException: The resulting value is outside the
> range for the data type REAL.
>
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
>
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
> Source)
>
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknow
> n Source)
>
> at
> org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown
> Source)
>
> at
> org.apache.derby.impl.jdbc.EmbedPreparedStatement.setFloat(Unknown Source)
>
> at
> com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.setParameter(Der
> bySampleBase.java:576)
>
> at
> com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.access$200(Derby
> SampleBase.java:71)
>
> at
> com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$1.perform(DerbyS
> ampleBase.java:515)
>
> at
> com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransac
> tion(DerbySampleBase.java:2755)
>
> ... 6 more
>
> Caused by: java.sql.SQLException: The resulting value is outside the range
> for the data type REAL.
>
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
> Source)
>
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossD
> RDA(Unknown Source)
>
> ... 15 more
>
> Caused by: ERROR 22003: The resulting value is outside the range for the
> data type REAL.
>
> at
> org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>
> at org.apache.derby.iapi.types.NumberDataType.normalizeREAL(Unknown
> Source)
>
> at org.apache.derby.iapi.types.SQLReal.setValue(Unknown Source)
>
> ... 11 more
>
>
>
> Thanks,
>
> Jim
>
--
Dag H. Wanvik
Sun Microsystems, Database Technology Group (DBTG)
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax: +47 73842101
RE: Is derby sql "real" equivalent to java "float"
Posted by Jim Newsham <jn...@referentia.com>.
Thanks for the pointer. It turns out I overlooked this documentation. I
was reading the pdf file, and only saw the overview of the data types, and
the assignability matrix.
I guess I'll have to test my values for legal range before inserting into
the database. It's unfortunate that there's a type range mismatch, though,
as it introduces an additional, potential cause of failure which I suspect
in most cases will not be expected or handled. If there is no longer a good
reason for the mismatch, I too vote that it be removed.
Thanks,
Jim
> -----Original Message-----
> From: Dag.Wanvik@Sun.COM [mailto:Dag.Wanvik@Sun.COM]
> Sent: Wednesday, October 31, 2007 1:32 PM
> To: Derby Discussion
> Subject: Re: Is derby sql "real" equivalent to java "float"
>
> Jim Newsham <jn...@referentia.com> writes:
>
> > In the meantime, I'd like to ask. Should Java float always be safely
> > settable to a field of Derby type REAL? This has been my assumption all
> > along, and refderby.pdf seems to imply this. Or, are there edge cases,
> such
> > as perhaps NaN which will cause the set operation to fail.
>
> The limits I mentioned in a previous post are actually documented:
>
> Quote from http://db.apache.org/derby/docs/dev/ref/rrefsqlj14122.html
>
> > REAL value ranges:
> >
> > * Smallest REAL value: -3.402E+38
> > * Largest REAL value: 3.402E+38
> > * Smallest positive REAL value: 1.175E-37
> > * Largest negative REAL value: -1.175E-37
> >
> > These limits are different from the java.lang.Float Java type limits.
> >
> > An exception is thrown when any double value is calculated or
> > entered that is outside of these value ranges.
>
> Thanks,
> Dag
Re: Is derby sql "real" equivalent to java "float"
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Jim Newsham <jn...@referentia.com> writes:
> In the meantime, I'd like to ask. Should Java float always be safely
> settable to a field of Derby type REAL? This has been my assumption all
> along, and refderby.pdf seems to imply this. Or, are there edge cases, such
> as perhaps NaN which will cause the set operation to fail.
The limits I mentioned in a previous post are actually documented:
Quote from http://db.apache.org/derby/docs/dev/ref/rrefsqlj14122.html
> REAL value ranges:
>
> * Smallest REAL value: -3.402E+38
> * Largest REAL value: 3.402E+38
> * Smallest positive REAL value: 1.175E-37
> * Largest negative REAL value: -1.175E-37
>
> These limits are different from the java.lang.Float Java type limits.
>
> An exception is thrown when any double value is calculated or
> entered that is outside of these value ranges.
Thanks,
Dag