Hi Eelco, Thank you very much for reporting. I've registered an issue for this: https://github.com/jOOQ/jOOQ/issues/5808
The reason why jOOQ generates these derived tables is documented here: https://github.com/jOOQ/jOOQ/issues/3579 Of course, the derived table wouldn't be necessary in your case, so we should try to avoid it. As a workaround, you could avoid UNION and use OR, instead, which might even be a bit better optimised in MySQL (I haven't measured this). So, rewrite your query to something like: where md.mediaId = 'xyz' and (exists (...) or exists (...)) Hope this helps, Lukas 2017-01-20 15:53 GMT+01:00 Eelco Hillenius <[email protected]>: > Hi, > > I converted the equivalent to this query to JOOQ: > > select a.mediaId > from contentApplication a > join mediaRelationship r on r.childId = a.mediaId > join module md on md.mediaId = r.parentId > where md.mediaId = 'xyz' > and exists ( > > select 1 > from sync_content as sc > join mediaRelationship r1 on r1.childId = sc.content_media_id -- > content -> collection > join mediaRelationship r2 on r2.childId = r1.parentId -- collection -> > anthology > where > r2.parentId = a.mediaId > and sc.module_media_id = md.mediaId > > union > > select 1 > from sync_content as sc > join mediaRelationship r1 on r1.childId = sc.content_media_id -- > content -> anthology > where > r1.parentId = a.mediaId > and sc.module_media_id = md.mediaId > ) > > The SQL code that JOOQ generates is: > > select `a`.`mediaId` > from `contentApplication` as `a` > join `mediaRelationship` as `r` > on `r`.`childId` = `a`.`mediaId` > join `module` as `md` > on `md`.`mediaId` = `r`.`parentId` > where ( > `md`.`mediaId` = 'xyz' > and exists ( > > select * > from ( > select 1 > from `sync_content` as `sc` > join `mediaRelationship` as `r1` > on `r1`.`childId` = `sc`.`content_media_id` > join `mediaRelationship` as `r2` > on `r2`.`childId` = `r1`.`parentId` > where ( > `r2`.`parentId` = `a`.`mediaId` > and `sc`.`module_media_id` = `md`.`mediaId` > ) > ) x > union > select * > from ( > select 1 > from `sync_content` as `sc` > join `mediaRelationship` as `r1` > on `r1`.`childId` = `sc`.`content_media_id` > where ( > `r1`.`parentId` = `a`.`mediaId` > and `sc`.`module_media_id` = `md`.`mediaId` > ) > ) x > ) > ) > > Note the wrapping on the statements in the union, which I believe is due > to "In case your database doesn't support ordered UNION subselects, the > subselects are nested in derived tables" described in > https://www.jooq.org/doc/3.9/manual/sql-building/sql- > statements/select-statement/union-clause (and I'm using MySQL). The > problem is that the correlations (`r2`.`parentId` = `a`.`mediaId`) fail > because jOOQ inserted a level. > > Is there a way around this (force JOOQ to not nest, or restructure)? > > Thanks, > > Eelco > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
