Search code examples
javahibernatehsqldb

Why is Hibernate adding schema name to Hsql functions?

We have a legacy app which uses Hibernate 3.0.3. We do unit testing with HSQLDB. One of the queries that is generated by Hibernate looks like this:

select
 transactio0_.REASON_ID as REASON1_0_,
 transactio0_.DESCRIPTION as DESCRIPT2_93_0_,
 transactio0_.REASON_NAME as REASON3_93_0_ 
from APPS.MTL_TRANSACTION_REASONS transactio0_ 
where transactio0_.REASON_ID=54
 and transactio0_.NVL (transactio0_.disable_date, NOW() + 1 DAY) > NOW()

Note that the schema name "transactio0_" is prepended to the NVL function. Why? How can we configure Hibernate so that does not do that?

The next question deals with the version of HSQLDB that we are using. We're currently using HSQLDB 2.2.8 and would like to move up to HSQLDB 2.3.2. The above query works fine with HSQLDB 2.2.8 but fails in HSQLDB 2.3.2 with an "invalid schema name" error. Is there any way that we can configure HSQLDB 2.3.2 so that it can successfully execute the query like HSQLDB 2.2.8 does?

Solution

  • The generated SQL looks strange and is certainly incorrect SQL. The transactio0_ is a table alias and shouldn't be prepended to any function name.

    Perhaps the selected Hibernate dialect is not the HSQLDB dialect.

    In any case, this is an old version of Hibernate and needs to be used with a version of HSQLDB that is closer to its time of release.

    Regarding configuring HSQLDB to accept the malformed name, it shouldn't be possible as the original behavior was fixed because it was too lax and accepted incorrect SQL.