Lukas, Thanks for looking into this. If it makes any difference, I can execute the query:
SELECT * FROM RESOURCE WHERE ID NOT IN (SELECT RESOURCEID_FK FROM RES_ISSUE_ASSIGNMENT UNION SELECT RESOURCEID_FK FROM RES_RECEIPT_ASSIGNMENT); directly on the derby database (e.g. from using ij or jOOQ string query and it executes fine, returning the expected result. So, does this mean that derby does support the nested query. I will try the not exists approach and see what happens. Here is an excellent related blog post: https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ On Monday, February 26, 2018 at 3:44:32 AM UTC-6, Lukas Eder wrote: > > 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 <manuelr...@gmail.com <javascript:>>: > >> 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 jooq-user+...@googlegroups.com <javascript:>. >> 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 jooq-user+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
