HSQLDB error while creating a table that contains DOUBLE type columns

Question

I am using HSQLDB for my integration tests to create tables in-memory. When i run my test, it fails to create the table for the entity classes that have double values. I get the below mentioned exception

    2603  [Datastore                 main       ERROR]  Error thrown 
    executing CREATE TABLE "simple"."SimpleObject"
    (
     "id" BIGINT GENERATED BY DEFAULT AS IDENTITY,
     "name" VARCHAR(40) NOT NULL,
     "notes" VARCHAR(4000) NULL,
     "salary" DOUBLE(4000) NULL,
     "version" TIMESTAMP NOT NULL,
     CONSTRAINT "SimpleObject_PK" PRIMARY KEY ("id")
     ) : unexpected token: ( : line: 6
    java.sql.SQLSyntaxErrorException: unexpected token: ( : line: 6
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)

But when i change the value to BigDecimal, it works. But I am really eager to find why DOUBLE have issues. Any help is much appreciated.

Answer 1

The reason is: it is not allowed by the SQL standard. The DOUBLE is the type for approximate floating point numbers. It cannot have a maximum length, like the VARCHAR type. Just use "salary" DOUBLE NULL.