0

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).

Rob D
  • 68
  • 5
  • You need to add the text of a query that passes HSQLDB but fails PostgreSQL. – fredt Nov 01 '19 at 20:48
  • Your URL is correct for checking ambiguous references (although it does not have sql.syntax_pg=true for PostgreSQL compatibility). It throws when the ORDER BY has a column name that is ambiguous. – fredt Nov 01 '19 at 22:08
  • Thanks very much for your quick response to this @fredt and apologies for the delay in responding. I've added an example of the SQL that is working in HSQLDB but failing in PostgreSQL. I've also tried the sql.syntax_pg=true setting, but it hasn't made any difference. The SQL still fails on PG but passes on HSQLDB. – Rob D Nov 04 '19 at 12:20
  • If you want to make sure your queries run properly with Postgres, the only way to do that is to run them against Postgres. –  Nov 04 '19 at 13:06
  • Thanks. In this case I did to show the fix was applied correctly. That's not really the point of my question however. I may want to run the app on SQL Server or Oracle. I would also need to set up the CI builds to run correctly & potentially simultaneously on Postgres, which isn't a small undertaking. The point is I'd like to detect this in HSQLDB, which the setting sql.enforce_refs=true suggests it should do, but it doesn't appear to work (for me). – Rob D Nov 04 '19 at 14:09
  • Small correction on the above comment(s) - the setting required (and tested) for Postgres compatibility is `sql.syntax_pgs=true` – Rob D Nov 05 '19 at 17:02

1 Answers1

1

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.

fredt
  • 24,044
  • 3
  • 40
  • 61
  • Thanks very much for the answer to this question (I hope it was clear enough and had the details you needed following my edit). The SQL fix ensures the order by is written as `order by f.id ASC nulls last` which doesn't reference the alias but the column directly in a non-ambiguous way. Is there anywhere I can track this progress of this issue in future versions of HSQLDB? We do provide the capability for devs to easily switch between Postgres and HSQLDB in development, but only use HSQLDB in CI builds, so Postgres is only really used on an ad-hoc basis for running tests. – Rob D Nov 07 '19 at 18:46
  • Your fix if fine. You can add a feature request to HSQLDB SourceForge site. – fredt Nov 10 '19 at 16:09