Hi,
I have a problem that is not JOOQ-specific, but because JOOQ is so good at
solving so many cross database issues, it highlights the shortcomings of
different DBs. My app runs in production on Oracle and MySQL and the
following query runs on both, but not on HSQLDB, which I use for all of my
unit testing:
val spacePath =
Factory.groupConcat(SPACES.NAME).orderBy(SPACES.ID).separator("/").as("path")
val parent =
t.select(SPACE_PATHS.as("d").DESCENDANT).select(spacePath).
from(SPACE_PATHS.as("d")).
join(SPACE_PATHS.as("a")).
on(SPACE_PATHS.as("a").DESCENDANT.equal(SPACE_PATHS.as("d").DESCENDANT)).
join(SPACES).
on(SPACES.ID.equal(SPACE_PATHS.as("a").ANCESTOR)).
where(SPACE_PATHS.as("d").ANCESTOR.equal(0)).
and(SPACE_PATHS.as("d").ANCESTOR.notEqual(SPACE_PATHS.as("d").DESCENDANT)).
groupBy(SPACE_PATHS.as("d").DESCENDANT).
having(spacePath.like("%" + parentPath + "%")).
fetchOne()
The reason for this is that HSQL apparently cannot use column labels in a
SELECT clause in the GROUP BY or HAVING clause. One option would be to
de-alias the definition of the GROUP_CONCAT column in the SELECT clause,
but because that it is in itself a portable clause (i.e. runs as LISTAGG in
Oracle), reformulating this seems to have maintenance overheads as well.
Alternatively, this query runs fine on H2, but the issue I have is that the
app has made heavy use of the MERGE syntax to implement UPSERTS in MySQL
and Oracle, which HSQLDB supports, but H2 has it's own syntax.
So I'm wondering what practical options are left - I was thinking something
along of the lines of detecting the dialect at runtime and issuing a HSQLDB
specific syntax for the purpose of the test suite. Not ideal, but it seems
to be the least worse solution.
Any thoughts?
Cheers,
Ben
