Hi Lukas, Thank you for taking the time with such a comprehensive response :-)
On Tuesday, August 21, 2012 8:48:21 AM UTC+1, Lukas Eder wrote: > > > 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)? Sure, no problem. I can do two things: - Just give you the cut down DDL, insert statements and the query I am sending; - The whole thing is part of a largish project that I can point you to, which runs the integration tests against HSQLDB, MySQL and Oracle as part of a Maven build; - Give you a cut down runnable solution (but you might want to give some input as to how that is easiest for you to consume); So first of all, here is the SQL-only scenario: CREATE TABLE spaces ( id int(11) NOT NULL, name varchar(50) NOT NULL, parent int(11) NOT NULL, PRIMARY KEY(id) ) ALTER TABLE spaces ADD CONSTRAINT name UNIQUE (name, parent) ALTER TABLE spaces ADD CONSTRAINT spaces_ibfk_1 FOREIGN KEY(parent) REFERENCES spaces(id) ON DELETE RESTRICT ON UPDATE RESTRICT CREATE TABLE space_paths ( ancestor int(11) NOT NULL, descendant int(11) NOT NULL, depth int(11) NOT NULL, PRIMARY KEY(ancestor,descendant) ) ALTER TABLE space_paths ADD CONSTRAINT space_paths_ibfk_2 FOREIGN KEY(descendant) REFERENCES spaces(id) ON DELETE RESTRICT ON UPDATE RESTRICT ALTER TABLE space_paths ADD CONSTRAINT space_paths_ibfk_1 FOREIGN KEY(ancestor) REFERENCES spaces(id) ON DELETE RESTRICT ON UPDATE RESTRICT INSERT INTO spaces(id, name, parent) VALUES(0, 'root', 0) INSERT INTO spaces(id, name, parent) VALUES(1, 'foo', 0) INSERT INTO spaces(id, name, parent) VALUES(2, 'bar', 1) INSERT INTO spaces(id, name, parent) VALUES(3, 'baz', 0) INSERT INTO spaces(id, name, parent) VALUES(4, 'bar', 3) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(0, 0, 0) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(0, 1, 1) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(0, 2, 2) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(0, 3, 1) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(0, 4, 2) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(1, 1, 0) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(1, 2, 1) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(2, 2, 0) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(2, 3, 0) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(3, 4, 1) INSERT INTO space_paths(ancestor, descendant, depth) VALUES(4, 4, 0) select d.descendant, group_concat(n.name order by n.id separator '/') as path from space_paths d join space_paths a on (a.descendant = d.descendant) join spaces n on (n.id = a.ancestor) where d.ancestor = 0 and d.descendant != d.ancestor group by d.descendant having path like 'root/baz%' If you want to see this as an example with JOOQ generation, here is the patch that I am working on to introduce hierarchical queries into our project: https://github.com/lshift/diffa/blob/1721ff3f19f24adbf2bbd4f816f4f92dc44285f3/kernel/src/main/scala/net/lshift/diffa/kernel/config/system/JooqSystemConfigStore.scala For completeness' sake, this is a part of a work in progress pull request (https://github.com/lshift/diffa/pull/255), but I don't want to bog you down with internal project details, since it is a 80K LOC project, written mainly in Scala. But the file linked above demonstrates the usage of the JOOQ API. > 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 > Very cool :-) Unfortunately in this project, we need to support MySQL, but if were only supporting Oracle, I probably would have replaced the hand cranked hierarchy with a recursive query. So I can see a lot of value for JOOQ to provide the abstraction over the DBs that support recursive queries. > > > 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... > Do you think I can do this by having the JOOQ Factory produce the dialect specific GROUP_CONCAT at runtime and then inject this as a string into the SELECT and HAVING clauses? I do take your point about it potentially being reasonable for HSQLDB not to support SELECT column labels in the GROUP BY/ HAVING clauses, it was just that the JOOQ generated query works fine on MySQL and H2. > > > 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. > Yes, it's a tricky one - potentially the cost of the generic solution is likely to be higher than sorting things out in your particular application. Maybe the only workable solution is to do something like what Hibernate does for UPSERTs (i.e. fire off multiple queries behind the scenes). This behavior would have to get marked as a workaround to avoid application boilerplate when using JOOQ with H2, in order to be clear about what is going on under the covers. Because H2 is mainly used as an embedded DB, maybe the overhead of firing off multiple requests to H2 is not as bad as if you were to do this with a network DB. > > 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 > > Ah yes - I've just realized that 558 is basically what I was proposing above (sorry for going on about something you've already thought about). > > 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. I think that this may be the most pragmatic solution for me. > > > Yes, and you could contribute this to #558 :-) > This would be the right thing to do :-) If the HSQLDB detection approach proves unfruitful, I can take a look at this as a fallback. > > > 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... > We do run Oracle and MySQL as part of the build on our continuous integration server, so at the end of the day, we could just give up on HSQLDB/H2. I'm just conscious of the slow down in development that this would have. Thanks for all of your input, it is appreciated. Cheers, Ben
