HSQL + Hibernate Exception: Wrong column type: Found: double, expected: float

Question

I am using in-memory HSQL (HSQLDB) with Hibernate for all my unit tests, because it is very fast. I have a table with a column defined as follows:

float qw;

When hibernate starts, I get the following error:

org.hibernate.HibernateException: Wrong column type in MyTable for column qw. 
Found: double, expected: float

Why does it find double when the column is declared as float?

Answer 1

This is happening due to a series of unfortunate events.

1. The problem begins with the fact that HSQLDB does not support the float data type. (Duh? Yes, I know, but Documentation here.)

2. The problem starts becoming ugly due to the fact that HSQLDB does not simply fail when you specify a float column, but it silently re-interprets it as double, pretending to the unsuspecting programmer that everything went fine. So, in your create table statement you may specify the type of a column as float, and HSQLDB will succeed, but it is only trolling you, because if you later query the type of that column, you will find that it is double, not float.

3. Then, later, hibernate finds this column to be double, while it expects it to be float, and it is not smart enough to make use of the fact that float is assignable from double. Everyone knows that a double is better than a float, so hibernate should actually be happy that it found a double while all it needed was a float, right? --but no, hibernate will not have any of that: when it expects a float, nothing but a float will do.

4. Then, there is this funny thing about hibernate supposedly having built-in support for HSQLDB, as evidenced by the fact that it includes a class org.hibernate.dialect.HSQLDialect, but the dialect does not care about your floats. So, they don't believe that a data type incompatibility is a dialect issue? they never tested it with floats? I don't know what to suppose, but the truth of the matter is that the hibernate dialect for HSQLDB does not provide any correction for this problem.

So, what can we do?

One possible solution to the problem is to create our own hibernate dialect for HSQLDB, in which we correct this discrepancy.

In the past I came across a similar problem with MySQL and boolean vs. bit, (see this question: "Found: bit, expected: boolean" after Hibernate 4 upgrade) so for HSQLDB I solved the problem with float vs. double by declaring my own HSQLDB dialect for hibernate:

/**
 * 'Fixed' HSQL Dialect.
 *
 * PEARL: HSQL seems to have a problem with floats.  We remedy this here.
 * See https://stackoverflow.com/q/28480714/773113
 *
 * PEARL: this class must be public, not package-private, and it must have a 
 * public constructor, otherwise hibernate won't be able to instantiate it.
 */
public class FixedHsqlDialect extends HSQLDialect
{
    public FixedHsqlDialect()
    {
        registerColumnType( java.sql.Types.FLOAT, "double" );
    }
}

And using it as follows:

ejb3cfg.setProperty( "hibernate.dialect", FixedHsqlDialect.class.getName() );
    //Instead of: org.hibernate.dialect.HSQLDialect.class.getName();