Re: trying to use union operation and getting an error in the translated SQL
Excellent. I will migrate to this solution Sent from my iPad > On Feb 27, 2018, at 3:06 AM, Lukas Eder wrote: > > Hi Manuel, > > Yes, the view approach is a very good one. The jOOQ code generator will deal > with your view as with any other table. If you plan to re-use the idea of an > ALL_RES_ASSIGNMENTS object, then database views would be my recommended > solution. > > Thanks, > Lukas > > 2018-02-26 23:10 GMT+01:00 Manuel Rossetti : >> Also, how does jOOQ handle views. Could I create a view of the union query >> and then reference the view. >> >> CREATE VIEW ALL_RES_ASSIGNMENTS (RESOURCEID_FK) AS >> (SELECT RESOURCEID_FK FROM RES_ISSUE_ASSIGNMENT >> UNION >> SELECT RESOURCEID_FK FROM RES_RECEIPT_ASSIGNMENT); >> >> SELECT * >> FROM RESOURCE >> WHERE NOT EXISTS (select * from ALL_RES_ASSIGNMENTS where RESOURCE.ID = >> RESOURCEID_FK); >> >> That is, can I reference a created view in jooq like a table? > > -- > You received this message because you are subscribed to a topic in the Google > Groups "jOOQ User Group" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/jooq-user/6uWwtl-4kpE/unsubscribe. > To unsubscribe from this group and all its topics, 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.
Re: trying to use union operation and getting an error in the translated SQL
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 : > One other thing to contemplate, could it be the union itself: > > SelectOrderByStep> 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 : >> >>> 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> 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 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,
Re: trying to use union operation and getting an error in the translated SQL
Hi Manuel, Yes, the view approach is a very good one. The jOOQ code generator will deal with your view as with any other table. If you plan to re-use the idea of an ALL_RES_ASSIGNMENTS object, then database views would be my recommended solution. Thanks, Lukas 2018-02-26 23:10 GMT+01:00 Manuel Rossetti : > Also, how does jOOQ handle views. Could I create a view of the union > query and then reference the view. > > CREATE VIEW ALL_RES_ASSIGNMENTS (RESOURCEID_FK) AS > (SELECT RESOURCEID_FK FROM RES_ISSUE_ASSIGNMENT > UNION > SELECT RESOURCEID_FK FROM RES_RECEIPT_ASSIGNMENT); > > SELECT * > FROM RESOURCE > WHERE NOT EXISTS (select * from ALL_RES_ASSIGNMENTS where RESOURCE.ID = > RESOURCEID_FK); > > That is, can I reference a created view in jooq like a table? > -- 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.
Re: trying to use union operation and getting an error in the translated SQL
Hi Manuel, 2018-02-26 22:43 GMT+01:00 Manuel Rossetti : > 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); > Yes indeed. The problem (in Derby) is really the nested select's asterisk. This is a missing feature in Derby, see: https://issues.apache.org/jira/browse/DERBY-6983 When fixing #7222 in jOOQ, the fix might include generating the actual column list instead of an asterisk (unless Derby will fix DERBY-6983 soon, in case of which jOOQ's workaround won't be necessary). https://github.com/jOOQ/jOOQ/issues/7222 > 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/ > Yes, I'm aware of this post, although beware that it is specific to SQL Server and from 2009, so I'm sure not everything applies in the same way for Derby -- 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.
Re: trying to use union operation and getting an error in the translated SQL
One other thing to contemplate, could it be the union itself: SelectOrderByStep> 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-in-and-null-values/ > > I hope this helps, > Lukas > > 2018-02-23 17:24 GMT+01:00 >: > >> 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> 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 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.
Re: trying to use union operation and getting an error in the translated SQL
Also, how does jOOQ handle views. Could I create a view of the union query and then reference the view. CREATE VIEW ALL_RES_ASSIGNMENTS (RESOURCEID_FK) AS (SELECT RESOURCEID_FK FROM RES_ISSUE_ASSIGNMENT UNION SELECT RESOURCEID_FK FROM RES_RECEIPT_ASSIGNMENT); SELECT * FROM RESOURCE WHERE NOT EXISTS (select * from ALL_RES_ASSIGNMENTS where RESOURCE.ID = RESOURCEID_FK); That is, can I reference a created view in jooq like a table? On Monday, February 26, 2018 at 3:43:13 PM UTC-6, Manuel Rossetti wrote: > > 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 : >> >>> 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> 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 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
Re: trying to use union operation and getting an error in the translated SQL
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 >: > >> 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> 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 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.
Re: trying to use union operation and getting an error in the translated SQL
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 : > 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> 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 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+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.
trying to use union operation and getting an error in the translated SQL
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> 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 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
