Thanks for your report. That is a very curious limitation of the Derby database. I have filed a feature request: https://issues.apache.org/jira/browse/DERBY-6983
Of course, jOOQ shouldn't generate the extra derived table (and SELECT * from it) in this trivial case. The reason why the derived table is generated is because some databases (including Derby) do not support nested set operations natively, so jOOQ emulates that using derived tables: https://github.com/jOOQ/jOOQ/issues/3579 But in cases where there is no set operation nesting, this emulation should not be applied. The emulation is already causing trouble elsewhere, e.g. in MySQL 8.0's recursive query support: https://github.com/jOOQ/jOOQ/issues/6431 I have created a new issue for your case: https://github.com/jOOQ/jOOQ/issues/7222 In the meantime, the workaround for you would be to use a NOT EXISTS predicate rather than NOT IN. I recommend this also because of NOT IN's behaviour in the presence of NULLs, i.e. if one of your RESOURCEID_FK columns is nullable, then your query is likely wrong. More details about this here: https://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-null-values/ I hope this helps, Lukas 2018-02-23 17:24 GMT+01:00 <[email protected]>: > I am trying to execute the following query: > > -- find all resources that do not have assignments > SELECT * > FROM RESOURCE > WHERE ID NOT IN (SELECT RESOURCEID_FK FROM RES_ISSUE_ASSIGNMENT > UNION > SELECT RESOURCEID_FK FROM RES_RECEIPT_ASSIGNMENT); > > My jooQ is like this: > > DSLContext dsl = getDSLContext(); > > SelectOrderByStep<Record1<Integer>> allAssignments = > dsl.select(RES_ISSUE_ASSIGNMENT.RESOURCEID_FK) > .from(RES_ISSUE_ASSIGNMENT) > > .union(dsl.select(RES_RECEIPT_ASSIGNMENT.RESOURCEID_FK).from(RES_RECEIPT_ASSIGNMENT)); > > Result<ResourceRecord> resourceRecords = > dsl.selectFrom(RESOURCE).where(RESOURCE.ID.notIn(allAssignments)).fetch(); > > > The created SQL is throwing a derby exception: > > > Exception in thread "main" org.jooq.exception.DataAccessException: SQL > [select "APP"."RESOURCE"."ID", "APP"."RESOURCE"."NAME", > "APP"."RESOURCE"."TERMINAL_FK", "APP"."RESOURCE"."CAPACITY", > "APP"."RESOURCE"."THROUGHPUT_GPM", "APP"."RESOURCE"."COMMENT" from > "APP"."RESOURCE" where "APP"."RESOURCE"."ID" not in (select * from (select > "APP"."RES_ISSUE_ASSIGNMENT"."RESOURCEID_FK" from > "APP"."RES_ISSUE_ASSIGNMENT") x union select * from (select > "APP"."RES_RECEIPT_ASSIGNMENT"."RESOURCEID_FK" from > "APP"."RES_RECEIPT_ASSIGNMENT") x)]; 'SELECT *' only allowed in EXISTS and > NOT EXISTS subqueries. > at org.jooq_3.10.4.DERBY.debug(Unknown Source) > at org.jooq.impl.Tools.translate(Tools.java:2239) > at > org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:704) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:361) > at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:317) > at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2597) > > > As one can see, the generated SQL contains "extra" select *. I don't > understand why this occurs. I am assuming that my jOOQ code is not > appropriate in some way, but after looking at the manual, I cannot tell why. > > Thanks! > > -- > 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.
