trying to use union operation and getting an error in the translated SQL

18 views
Skip to first unread message

manuelr...@gmail.com

unread,
Feb 23, 2018, 1:06:48 PM2/23/18
to jOOQ User Group
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!

Lukas Eder

unread,
Feb 26, 2018, 4:44:32 AM2/26/18
to jooq...@googlegroups.com
Thanks for your report.

That is a very curious limitation of the Derby database. I have filed a feature request:

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:

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:

I have created a new issue for your case:

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:

I hope this helps,
Lukas

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Manuel Rossetti

unread,
Feb 26, 2018, 4:43:13 PM2/26/18
to jOOQ User Group
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:

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

Manuel Rossetti

unread,
Feb 26, 2018, 5:10:07 PM2/26/18
to jOOQ User Group
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?

Manuel Rossetti

unread,
Feb 26, 2018, 5:21:10 PM2/26/18
to jOOQ User Group
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:

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
Lukas Eder
unread,
Feb 27, 2018, 4:02:52 AM2/27/18
to jooq...@googlegroups.com
Hi Manuel,


2018-02-26 22:43 GMT+01:00 Manuel Rossetti <manuelr...@gmail.com>:
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:

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).
 
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:


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


Lukas Eder
unread,
Feb 27, 2018, 4:06:22 AM2/27/18
to jooq...@googlegroups.com
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
Lukas Eder
unread,
Feb 27, 2018, 4:08:02 AM2/27/18
to jooq...@googlegroups.com
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:

Thanks,
Lukas


To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
Manuel Rossetti
unread,
Feb 27, 2018, 7:48:12 AM2/27/18
to jooq...@googlegroups.com
Excellent. I will migrate to this solution 

Sent from my iPad


-- 

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+...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages