Hi Ben, > 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.
You can call it shortcomings. Or just the better databases' features :-) > 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: > > [query...] That looks like an interesting integration test query. Care to share it (and the underlying test data in the SPACE_PATHS, SPACES tables and data)? I might make that a part of jOOQ's integration tests. It looks as thought this would be useful for additional hierarchical query tests as well. I'm hoping to be able to implement #668 some time soon: https://github.com/jOOQ/jOOQ/issues/668 > The reason for this is that HSQL apparently cannot use column labels in a > SELECT clause in the GROUP BY or HAVING clause. You mean the fact that you're using an aliased version of your GROUP_CONCAT expression in the HAVING clause? If so, then I'd say that HSQLDB behaves correctly. The scope of column aliases limits their use to - Declarations in the SELECT clause - Usage in the ORDER BY clause (and outer queries) To understand this, look at a SELECT statement in a "different way". The following depicts the formal internal order of various clauses of the SELECT statement: subquery ::= - FROM (incl. JOIN, PIVOT, FLASHBACK, etc.): Declares table expressions - WHERE: Reduces table expressions - CONNECT BY: For hierarchical queries - GROUP BY: Reorganises table expressions - HAVING: Reduces table expressions - SELECT: Projects column expressions query ::= - UNION/INTERSECT/EXCEPT: Connects several subqueries - ORDER BY: Orders connected subqueries - LIMIT: Limits result sets As you can see, the SELECT clause is quite far down. Many tools / frameworks reflect this "improved" order by allowing to write queries like FROM [table] WHERE [...] SELECT [...]. These include - LINQ-to-SQL - Hibernate HQL - JPA To wrap it up, try using an "unaliased" version of the GROUP_CONCAT aggregate function in the HAVING clause, aliasing it only in the SELECT clause... > 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. Yes, that's a bit of a pain. H2's MERGE is currently simulated using standard SQL MERGE statements in other databases. The same applies for MySQL/CUBRID's ON DUPLICATE KEY UPDATE clause, which can be simulated using the SQL MERGE statement. So far, I have not found a way of simulating H2's MERGE in MySQL, or MySQL's ON DUPLICATE KEY UPDATE in H2. Any ideas are welcome, though. Note, there is a pending feature request to run MySQL's ON DUPLICATE KEY UPDATE in two separate queries: https://github.com/jOOQ/jOOQ/issues/558 > 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. Yes, and you could contribute this to #558 :-) > Any thoughts? Is running your test suites against an Oracle XE or MySQL database (both available for free), instead of introducing a third database not an option? I'm aware that they're both slower databases to set up, but the degree of complexity of supporting 3-4 dialects in a single application becomes hard to handle, even when using jOOQ. As you've seen, jOOQ itself cannot abstract everything... Cheers Lukas
