HSQLDB and JOOQ code generation

Question

I'm trying to use JOOQ to connect to HSQLDB but I'm failing, here is the error message:

INFO: Initialising properties  : /codegen.xml
2014-02-09T09:55:19.542+0100  SEVERE  Error while fetching check constraints
org.jooq.exception.DataAccessException: SQL [select "tc"."TABLE_SCHEMA", "tc"."TABLE_NAME", "cc"."CONSTRAINT_NAME", "cc"."CHECK_CLAUSE" from "INFORMATION_SCHEMA"."TABLE_CONSTRAINTS" as "tc" join "INFORMATION_SCHEMA"."CHECK_CONSTRAINTS" as "cc" using( "CONSTRAINT_CATALOG", "CONSTRAINT_SCHEMA", "CONSTRAINT_NAME") where "tc"."TABLE_SCHEMA" in (cast(? as varchar(128)))]; user lacks privilege or object not found: cc.CONSTRAINT_NAME

This query fails also on the HSQLDB Manager (with the same error of the JOOQ code generator):

select "tc"."TABLE_SCHEMA", "tc"."TABLE_NAME", "cc"."CHECK_CLAUSE" , "cc"."CONSTRAINT_NAME"
from "INFORMATION_SCHEMA"."TABLE_CONSTRAINTS" as "tc" 
    join "INFORMATION_SCHEMA"."CHECK_CONSTRAINTS" as "cc" 
    using( "CONSTRAINT_CATALOG", "CONSTRAINT_SCHEMA", "CONSTRAINT_NAME")

This query works fine:

select "tc"."TABLE_SCHEMA", "tc"."TABLE_NAME", "cc"."CHECK_CLAUSE" , "CONSTRAINT_NAME"
from "INFORMATION_SCHEMA"."TABLE_CONSTRAINTS" as "tc" 
    join "INFORMATION_SCHEMA"."CHECK_CONSTRAINTS" as "cc" 
    using( "CONSTRAINT_CATALOG", "CONSTRAINT_SCHEMA", "CONSTRAINT_NAME")

I think the problem is on the "cc" qualifier (on the "CONSTRAINT_NAME" field returned by the query), is this a HSQLDB bug?

I'm using:

HSQLDB 2.3.1
JOOQ 3.2.3

Thanks Ciccio

Removing the "Using" clause on the join (using the on clause) the query works correctly. Is it possible to force JOOQ to use on and not using ? — CIccio
I just want to let you know that I've find the solution by changing the class HSQLDBDatabase that contains the clause ".using" in the clause ".on" and now it is working correclty.. I think.. I'll let you know. — CIccio

Lukas Eder Lukas Eder · Accepted Answer · 2014-02-10T09:34:21

This is a bug in jOOQ 3.2. I have created an issue for this: #3019. It is a side-effect of a more general bug related to jOOQ's current interpretation of JOIN .. USING and of NATURAL JOIN: #2808.

The bug should only be cosmetic, though, without any impact on your code generation.

HSQLDB and JOOQ code generation

1 Answers