Hi Manuel, I'm not sure what you mean by "other thing" in this context... In any case, this issue will be addressed on the jOOQ side by avoiding the generation of select * from (...) queries when not strictly necessary. The fix will be done through: https://github.com/jOOQ/jOOQ/issues/7224
Thanks, Lukas 2018-02-26 23:21 GMT+01:00 Manuel Rossetti <manuelrosse...@gmail.com>: > One other thing to contemplate, could it be the union itself: > > 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)); > > System.out.println(allAssignments.getSQL()); > > > Results 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 > > > 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-i >> n-and-null-values/ >> >> I hope this helps, >> Lukas >> >> 2018-02-23 17:24 GMT+01:00 <manuelr...@gmail.com>: >> >>> 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. >>> 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. > -- 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.
