Search code examples
postgresqlhsqldb

Enforcing non-ambiguous references to column names in HSQLDB

I have an HQL query which fails on PostgreSQL as the order by clause includes an ambiguous column reference. I understand the cause of the problem and how to fix it, however, unit tests testing the same query & order by clause are passing with no error when running against an in memory HSQLDB database. I want to ensure all of my unit tests running on HSQLDB in CI builds will fail if they encounter similar issues in other queries.

Reading the HSQLDB guide (http://hsqldb.org/doc/2.0/guide/guide.pdf) reveals there are several settings that are disabled by default, where enabling them will enforce checks that DB object & queries conform to SQL standards.

I believe the setting I need is sql.enforce_refs=true on my JDBC connect URL in order for the test to fail when the ambiguous reference is encountered.

I've updated my URL so it now looks as below.

jdbc.url=jdbc:hsqldb:mem:testdb;shutdown=false;sql.enforce_refs=true;sql.restrict_exec=true;sql.enforce_type=true

(Note: I also included sql.enforce_names=true but this caused a lot of failures which I have not yet investigated).

The problem I have is that even with these settings in the connect URL the tests continue to pass. It seems HSQLDB isn't enforcing the check. I've not found any issues reported about this setting, and I'm using the latest HSQLDB version (2.5.0 currently).

My problem is I want to enforce this check via the connect URL setting in order to detect these problems at the time the CI build executes unit tests, and the setting doesn't seem to work, so I'd like to know if this is a known problem, or if I've done something wrong, or if someone else has managed to enable this check in a different way? (I don't really want to execute the SET DATABASE... command to enable this check).

Many thanks, Rob

Edit: 4/11/19

Example SQL included to aid with problem resolution. Please note, my problem is not with this SQL, it is with using the options in the HSQLDB connect string to detect this kind of SQL problem when running tests in the CI build.

    select
        f.ID as ID1_98_,
        f.PROP_A as PROP_A2_98_,
        f.CLOSE_DATE as CLOSE_DA3_98_,
    from
        FOO f 
    left outer join
        FOO_SUB_TYPE fst 
            on f.FOO_SUB_TYPE_FK=fst.ID 
    left outer join
        FOO_TYPE ft 
            on fst.ID=ft.ID 
    where
        ?=f.WIBBLE_FK 
    order by
        id ASC nulls last

The SQL is generated from the Hibernate HQL as below:

from Foo f
left outer join f.fooSubType as fst
where (:wibbleId = f.wibble.id)

I'm not the author of the HQL / SQL (except for changing table / entity class names here) and in my opinion the left outer join is unnecessary. As I said, the SQL is not really very important, the issue is being able to detect the problem of ambiguous references in SQL / HQL (of which there are many in the codebase).

Solution

  • The query generated by Hibernate has f.ID as ID1_98_. The ORDER BY id clause should fail in strict SQL because it has renamed the ID column.

    Over 10 years ago, HSQLDB accepted this query for compatibility with some other databases. In recent years, the compatibility settings were added to allow strict checks. The sql.enforce_refs covers the case where there are two columns named ID in the select list but it does not cover this case. We may add this case in the next version.

    Regarding using HSQLDB for database application testing, you still need to run tests with the intended target from time to time to avoid false positive results.