We use a row of type DOUBLE and put in the value
Double.NaN, via the JDBC interface. This results in an
exception complaining that NaN is not a valid row
name. We think that Double.NaN should mapped to SQL
value NULL and vice versa.
I don't think so. We should stick to SQL standards when
dealing with SQL statements. Double.NaN is a Java
programming language construct. You should use the
PrepareStatement() method to insert this value.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
For 1.7.0 RC3 the value will be set to NULL if double and
float values of NaN and infinity are set via
PreparedStatement.setFloat and setDouble. Pass to Campbell
for QA.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2002-05-14
milestone: --> current-release
labels: --> engine
status: open --> open-remind
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2002-05-14
Logged In: YES
user_id=199381
There are still problems w.r.t. internal assigment. That
is, it is possible for SQL functions and stored procedures
to directly insert/update to Java java.lang.Double.NaN and
POSITIVE/NEGATIVE ININITY values in DOUBLE, REAL, and FLOAT
columns (although it is no longer possible to do this with
java.lang.Float.NaN et. al., since we have upgraded our
REAL storage to type java.lang.Double from
java.lang.Float). The JDBC patch is a good start to
separate Java from SQL concerns, but not a complete
solution. We must detect and either convert or disallow
deeper in the engine (in Column.java?) as well, or else we
reflect an inconsistent policy that can lead to errors.
Of course, the other way to go is to eventually bring more
Java flavour into the SQL dimension, allowing Java static
final values to be referenced from HSQLDB SQL syntax or to
provide org.hsqldb.Library methods that supply the
Double.NaN and P/N Infinity values...
We should discuss a bit further, try to decide one way or
the other (probably the pure SQL way), and close the
remaining holes or add the missing features.
Talk to you soon.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2002-05-18
priority: 1 --> 5
assigned_to: boucherb --> fredt
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2002-05-18
Logged In: YES
user_id=199381
Fred:
Below is my Q/A evaluation. I have reassigned this item
back to you and bumped up the pri again so you will see
this on your list. If you want me to work on developing a
complete solution, just reassign back to me with a
comment. Otherwise, I think what I have stated below is a
good start on the "missing" requirments that were not
addressed by the JDBC patch.
Cheers,
Campbell
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The issue has been resolved as far as I can tell. This is
a JDBC requirement not an SQL one. So NaN should be turned
into NULL when sent to the DB but JDBC does not say that
you can't get a NaN back from the DB or that NULL values
should be translated into NaN.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2002-05-19
Logged In: YES
user_id=199381
Ok. My understanding of what you have just said is that we
should stick to the SQL spec and not introduce any Java-
specific aspects into SQL number handling. I think that is
great and agree totally.
Given such a descision, we sould not allow
Float/Double.NaN, Float/Double.POSITIVE_INFINITY, or
Float/Double.NEGATIVE_INFINITY to be stored directly at all
in any HSQLDB column type. This is now a different issue
with Float.NaN, etc., since we have upgraded all
approximate numeric types to be stored as
java.lang.Double. That is, Java itself will throw for
Float.NaN, etc., without some intervention on our part. But
regardless, what I am really pushing for here is that we
should be converting Float/Double.NaN,
Float/Double.POSITIVE_INFINITY and
Float/Double.NEGATIVE_INFINITY to SQL NULL (Java null) at a
lower level, i.e. in Column, rather than in the jdbcXXX
classes, the reason being that the JDBC conversion can be
circumvented by performing inserts and updates using values
returned by Java methods called as SQL functions. In other
words, the JDBC patch presents an inconsistent policy: on
the one hand, it is impossible insert/update a column with
a Java NaN or infinity via JDBC, whereas it is possible
using SQL functions. Moreover, Java Nan and infinity
values and the results of expressions containing them, to
the best of my reading of the SQL92 spec for numbers
(section 4.4 and 6.12), violate parts of the
specification. So, we have prevented values not in the SQL
number domain from being introduced using the JDBC
interface, but we have not yet taken the same measures
w.r.t. SQL functions. To me, the natural place to put code
that guards against non-SQL values being stored in Java
object representations (that otherwise can store such
values) is at the lowest level: Column, so that *all* such
attempts are handled properly, rather than just those
coming from JDBC.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2002-10-13
Logged In: YES
user_id=199381
Here is a firm and fatal example of what I was talking
about before (using the latest 1.7.1 RC):
CREATE TABLE DOUBLE_TEST(VALUE DOUBLE)
INSERT INTO DOUBLE_TEST VALUES(cot(0))
SHUTDOWN
upon restart:
C:\WINNT\Profiles\campbell.000
\Desktop\hsqldb\demo>cd ..\data
java.sql.SQLException: Column not found: Column not
found: INFINITY in statement [INSERT INTO
DOUBLE_TEST VALUES(Infinity)]
at org.hsqldb.Trace.getError(Unknown Source)
at org.hsqldb.Log.runScript(Unknown Source)
at org.hsqldb.Log.open(Unknown Source)
at org.hsqldb.Database$Logger.openLog(Unknown
Source)
at org.hsqldb.Database.open(Unknown Source)
at org.hsqldb.Database.<init>(Unknown Source)
at org.hsqldb.jdbcConnection.openStandalone
(Unknown Source)
at org.hsqldb.jdbcConnection.<init>(Unknown
Source)
at org.hsqldb.jdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection
(DriverManager.java:512)
at java.sql.DriverManager.getConnection
(DriverManager.java:171)
at
org.hsqldb.util.ConnectionDialog.createConnection
(Unknown Source)
at
org.hsqldb.util.ConnectionDialog.actionPerformed
(Unknown Source)
at java.awt.Button.processActionEvent
(Button.java:381)
at java.awt.Button.processEvent(Button.java:350)
at java.awt.Component.dispatchEventImpl
(Component.java:3598)
at java.awt.Component.dispatchEvent
(Component.java:3439)
at java.awt.EventQueue.dispatchEvent
(EventQueue.java:450)
at
java.awt.EventDispatchThread.pumpOneEventForHierarch
y(EventDispatchThread.java:197)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy
(EventDispatchThread.java:150)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy
(EventDispatchThread.java:140)
at java.awt.Dialog.show(Dialog.java:538)
at org.hsqldb.util.ConnectionDialog.create
(Unknown Source)
at
org.hsqldb.util.ConnectionDialog.createConnection
(Unknown Source)
at
org.hsqldb.util.DatabaseManager.actionPerformed
(Unknown Source)
at java.awt.MenuItem.processActionEvent
(MenuItem.java:588)
at java.awt.MenuItem.processEvent
(MenuItem.java:548)
at java.awt.MenuComponent.dispatchEventImpl
(MenuComponent.java:285)
at java.awt.MenuComponent.dispatchEvent
(MenuComponent.java:273)
at java.awt.EventQueue.dispatchEvent
(EventQueue.java:452)
at
java.awt.EventDispatchThread.pumpOneEventForHierarch
y(EventDispatchThread.java:197)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy
(EventDispatchThread.java:150)
at java.awt.EventDispatchThread.pumpEvents
(EventDispatchThread.java:144)
at java.awt.EventDispatchThread.pumpEvents
(EventDispatchThread.java:136)
at java.awt.EventDispatchThread.run
(EventDispatchThread.java:99)
Our first solution was to filter out incoming +/- infinity
and NaN values w.r.t. the JDBC interface
implementations, but as indicated above, my later
observations were correct: we must deal with this at a
deeper level or it has the potential to do things as bad
as preventing a restart of the engine. Obviously, there
are lesser things that can also happen.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the bug report. Fixed and committed to hsqldb-dev
module.
The current behaviour is:
Turn values to null when submitted via JDBC in order to conform
to JDBC standard requirement.
Evaluate, store and return the full range of Double values
including NaN and +-Infinity internally.
Fully conforming to Java artithmetic rules for Double
Non conforming to SQL standard in division by 0 (6.12 General
Rule 4)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Anonymous
-
2002-10-13
Logged In: YES
user_id=199381
Thanks very much Fred.
Have you considered the case where an HSQLDB
database is transfered to another HSQLDB database, via
the transfer tool? Because this goes through JDBC, do
we not run the risk of making an "imprefect" copy?
I'll try to think of some more concrete examples, though
I'm currently at a loss: I just have this gut feeling still
that somehow having mixed policies will inevitably
result in, if not incorrect, at least
unintuitive/incosistent behavoiur in some (unexpected?)
places down the road (for instance, where values are
inserted/updated via stored procedures that use the
Connection method parameter to access the database).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Transfer through JDBC would not cause problems if prepared
statements are used - NaN and infinity will be translated to NULL.
It could cause problems otherwise, but it is up to the user to
dedice what they want to do with their database.
The main issue for me is to allow seamless arithmetic processing
within the engine. The SQL 92 standard does not conform to
IEEE and as such is crippled with respect to floating point types.
So I would happily forgo conformance to the SQL division by 0
rule and have a prope system that can be used for scientific and
engineering work. Inside the engine, everything is intuitive and
predictable. In some ways more so than any database engine.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Logged In: YES
user_id=32217
Beginning to check today...
Logged In: YES
user_id=32217
the driver will probably need to be changed here...also, do
we have a syntax problem for ANTLR?
2.0 or 1.7?
dedmike
Logged In: YES
user_id=150940
I don't think so. We should stick to SQL standards when
dealing with SQL statements. Double.NaN is a Java
programming language construct. You should use the
PrepareStatement() method to insert this value.
Logged In: YES
user_id=150940
There is a problem inserting NaN values for Foat and
Double. Changing to BUG.
Logged In: YES
user_id=150940
There is a unit test in 1.7.0 RC2 that demonstrates this.
Logged In: YES
user_id=150940
For 1.7.0 RC3 the value will be set to NULL if double and
float values of NaN and infinity are set via
PreparedStatement.setFloat and setDouble. Pass to Campbell
for QA.
Logged In: YES
user_id=199381
There are still problems w.r.t. internal assigment. That
is, it is possible for SQL functions and stored procedures
to directly insert/update to Java java.lang.Double.NaN and
POSITIVE/NEGATIVE ININITY values in DOUBLE, REAL, and FLOAT
columns (although it is no longer possible to do this with
java.lang.Float.NaN et. al., since we have upgraded our
REAL storage to type java.lang.Double from
java.lang.Float). The JDBC patch is a good start to
separate Java from SQL concerns, but not a complete
solution. We must detect and either convert or disallow
deeper in the engine (in Column.java?) as well, or else we
reflect an inconsistent policy that can lead to errors.
Of course, the other way to go is to eventually bring more
Java flavour into the SQL dimension, allowing Java static
final values to be referenced from HSQLDB SQL syntax or to
provide org.hsqldb.Library methods that supply the
Double.NaN and P/N Infinity values...
We should discuss a bit further, try to decide one way or
the other (probably the pure SQL way), and close the
remaining holes or add the missing features.
Talk to you soon.
Logged In: YES
user_id=199381
Fred:
Below is my Q/A evaluation. I have reassigned this item
back to you and bumped up the pri again so you will see
this on your list. If you want me to work on developing a
complete solution, just reassign back to me with a
comment. Otherwise, I think what I have stated below is a
good start on the "missing" requirments that were not
addressed by the JDBC patch.
Cheers,
Campbell
Logged In: YES
user_id=150940
The issue has been resolved as far as I can tell. This is
a JDBC requirement not an SQL one. So NaN should be turned
into NULL when sent to the DB but JDBC does not say that
you can't get a NaN back from the DB or that NULL values
should be translated into NaN.
Logged In: YES
user_id=199381
Ok. My understanding of what you have just said is that we
should stick to the SQL spec and not introduce any Java-
specific aspects into SQL number handling. I think that is
great and agree totally.
Given such a descision, we sould not allow
Float/Double.NaN, Float/Double.POSITIVE_INFINITY, or
Float/Double.NEGATIVE_INFINITY to be stored directly at all
in any HSQLDB column type. This is now a different issue
with Float.NaN, etc., since we have upgraded all
approximate numeric types to be stored as
java.lang.Double. That is, Java itself will throw for
Float.NaN, etc., without some intervention on our part. But
regardless, what I am really pushing for here is that we
should be converting Float/Double.NaN,
Float/Double.POSITIVE_INFINITY and
Float/Double.NEGATIVE_INFINITY to SQL NULL (Java null) at a
lower level, i.e. in Column, rather than in the jdbcXXX
classes, the reason being that the JDBC conversion can be
circumvented by performing inserts and updates using values
returned by Java methods called as SQL functions. In other
words, the JDBC patch presents an inconsistent policy: on
the one hand, it is impossible insert/update a column with
a Java NaN or infinity via JDBC, whereas it is possible
using SQL functions. Moreover, Java Nan and infinity
values and the results of expressions containing them, to
the best of my reading of the SQL92 spec for numbers
(section 4.4 and 6.12), violate parts of the
specification. So, we have prevented values not in the SQL
number domain from being introduced using the JDBC
interface, but we have not yet taken the same measures
w.r.t. SQL functions. To me, the natural place to put code
that guards against non-SQL values being stored in Java
object representations (that otherwise can store such
values) is at the lowest level: Column, so that *all* such
attempts are handled properly, rather than just those
coming from JDBC.
Logged In: YES
user_id=199381
Here is a firm and fatal example of what I was talking
about before (using the latest 1.7.1 RC):
CREATE TABLE DOUBLE_TEST(VALUE DOUBLE)
INSERT INTO DOUBLE_TEST VALUES(cot(0))
SHUTDOWN
upon restart:
C:\WINNT\Profiles\campbell.000
\Desktop\hsqldb\demo>cd ..\data
java.sql.SQLException: Column not found: Column not
found: INFINITY in statement [INSERT INTO
DOUBLE_TEST VALUES(Infinity)]
at org.hsqldb.Trace.getError(Unknown Source)
at org.hsqldb.Log.runScript(Unknown Source)
at org.hsqldb.Log.open(Unknown Source)
at org.hsqldb.Database$Logger.openLog(Unknown
Source)
at org.hsqldb.Database.open(Unknown Source)
at org.hsqldb.Database.<init>(Unknown Source)
at org.hsqldb.jdbcConnection.openStandalone
(Unknown Source)
at org.hsqldb.jdbcConnection.<init>(Unknown
Source)
at org.hsqldb.jdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection
(DriverManager.java:512)
at java.sql.DriverManager.getConnection
(DriverManager.java:171)
at
org.hsqldb.util.ConnectionDialog.createConnection
(Unknown Source)
at
org.hsqldb.util.ConnectionDialog.actionPerformed
(Unknown Source)
at java.awt.Button.processActionEvent
(Button.java:381)
at java.awt.Button.processEvent(Button.java:350)
at java.awt.Component.dispatchEventImpl
(Component.java:3598)
at java.awt.Component.dispatchEvent
(Component.java:3439)
at java.awt.EventQueue.dispatchEvent
(EventQueue.java:450)
at
java.awt.EventDispatchThread.pumpOneEventForHierarch
y(EventDispatchThread.java:197)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy
(EventDispatchThread.java:150)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy
(EventDispatchThread.java:140)
at java.awt.Dialog.show(Dialog.java:538)
at org.hsqldb.util.ConnectionDialog.create
(Unknown Source)
at
org.hsqldb.util.ConnectionDialog.createConnection
(Unknown Source)
at
org.hsqldb.util.DatabaseManager.actionPerformed
(Unknown Source)
at java.awt.MenuItem.processActionEvent
(MenuItem.java:588)
at java.awt.MenuItem.processEvent
(MenuItem.java:548)
at java.awt.MenuComponent.dispatchEventImpl
(MenuComponent.java:285)
at java.awt.MenuComponent.dispatchEvent
(MenuComponent.java:273)
at java.awt.EventQueue.dispatchEvent
(EventQueue.java:452)
at
java.awt.EventDispatchThread.pumpOneEventForHierarch
y(EventDispatchThread.java:197)
at
java.awt.EventDispatchThread.pumpEventsForHierarchy
(EventDispatchThread.java:150)
at java.awt.EventDispatchThread.pumpEvents
(EventDispatchThread.java:144)
at java.awt.EventDispatchThread.pumpEvents
(EventDispatchThread.java:136)
at java.awt.EventDispatchThread.run
(EventDispatchThread.java:99)
Our first solution was to filter out incoming +/- infinity
and NaN values w.r.t. the JDBC interface
implementations, but as indicated above, my later
observations were correct: we must deal with this at a
deeper level or it has the potential to do things as bad
as preventing a restart of the engine. Obviously, there
are lesser things that can also happen.
Logged In: YES
user_id=150940
Thanks for the bug report. Fixed and committed to hsqldb-dev
module.
The current behaviour is:
Turn values to null when submitted via JDBC in order to conform
to JDBC standard requirement.
Evaluate, store and return the full range of Double values
including NaN and +-Infinity internally.
Fully conforming to Java artithmetic rules for Double
Non conforming to SQL standard in division by 0 (6.12 General
Rule 4)
Logged In: YES
user_id=199381
Thanks very much Fred.
Have you considered the case where an HSQLDB
database is transfered to another HSQLDB database, via
the transfer tool? Because this goes through JDBC, do
we not run the risk of making an "imprefect" copy?
I'll try to think of some more concrete examples, though
I'm currently at a loss: I just have this gut feeling still
that somehow having mixed policies will inevitably
result in, if not incorrect, at least
unintuitive/incosistent behavoiur in some (unexpected?)
places down the road (for instance, where values are
inserted/updated via stored procedures that use the
Connection method parameter to access the database).
Logged In: YES
user_id=150940
Transfer through JDBC would not cause problems if prepared
statements are used - NaN and infinity will be translated to NULL.
It could cause problems otherwise, but it is up to the user to
dedice what they want to do with their database.
The main issue for me is to allow seamless arithmetic processing
within the engine. The SQL 92 standard does not conform to
IEEE and as such is crippled with respect to floating point types.
So I would happily forgo conformance to the SQL division by 0
rule and have a prope system that can be used for scientific and
engineering work. Inside the engine, everything is intuitive and
predictable. In some ways more so than any database engine.