HSQL upgrade to 1.8: ... DEFAULT 'NaN' changed to ... DEFAULT 0E0/0E0 in database.script causes HsqlException: unexpected token / in HSQL 2.3.2

Question

I have an application using HSQL 1.7.2 that contains table definitions with default values of NaN for some of the DOUBLE columns. After issuing, with 1.7.2,

SET SCRIPTFORMAT TEXT
SHUTDOWN SCRIPT

the following appears in the database.script file:

CREATE TABLE ... DOUBLE DEFAULT 'NaN' NOT NULL ...

I'm attempting to upgrade to HSQL 2.3.2, but I first have to upgrade to 1.8. I'm finding that after the upgrade to 1.8, the database.script file has:

CREATE TABLE ... DOUBLE DEFAULT 0E0/0E0 NOT NULL ...

When I open this database with HSQL 2.3.2, I get "SQLException: error in script file line ..." on the line at which "/" first appears. The traceback includes "Caused by: org.hsqldb.HsqlException: unexpected token: /".

I've messed around a lot with double_nan=false, without success.

Does anybody have any suggestions for me?

Answer 1

Support for NaN as default value was dropped in version 2.x. This will be restored in the next update.

For the time being, change the default to NULL and add a TRIGGER on the table to convert NULL entries to NaN.

The property hsqldb.double_nan=false must be specified or the statement SET DATABASE SQL DOUBLE NAN FALSE executed for the database to accept the NaN data values .