Hi Stan, This issue is now fixed for jOOQ 3.4.0, and might be merged to 3.3.3 and 3.2.6 although I cannot promise the merge, as the change was quite complex and it currently depends on changes to jOOQ's internals for jOOQ 3.4.0
The fix is documented here: https://github.com/jOOQ/jOOQ/issues/2335 Essentially, instead of just blindly selecting *: select * from ( select "limit_9992791".*, rownum as "rownum_9992791" from ( select "FOLDER"."ID", "SUBJECT"."ID" from "FOLDER" left outer join "SUBJECT" on "FOLDER"."LANDLORD" = "SUBJECT"."ID" ) "limit_9992791" where rownum <= (? + ?)) where "rownum_9992791" > ? ... we now rename columns in each subselect: select col_1 "ID", col_2 "ID" -- (ID, ID)from ( select "limit_9992791".*, rownum as "rownum_9992791" -- (col_1, col_2, rownum_9992791) from ( select "FOLDER"."ID" col_1, "SUBJECT"."ID" col_2 -- (col_1, col_2) from "FOLDER" left outer join "SUBJECT" on "FOLDER"."LANDLORD" = "SUBJECT"."ID" ) "limit_9992791" where rownum <= (? + ?)) where "rownum_9992791" > ? The actual solution doesn't look like this, but you get the idea. The point here is that columns need to be renamed in the inner-most subselect, in order to avoid ambiguous columns in a subselect which is prohibited in all SQL dialects. This was issue #2335. Then, in the outer-most select, the columns are renamed back again to their original names. As a "side-effect", the column count will now stay unchanged, which will fix your issue, where you had too many columns in an IN predicate's subquery. Cheers, Lukas 2014-04-17 10:50 GMT+02:00 Stanislas Nanchen <[email protected]>: > Hi! > > We just encountered the following problem using LIMIT in oracle (11....) > with the following query. (JOOQ 3.2.2) > > .delete(RC_BOX) > .where( > RC_BOX.ID.in(DSL > .select(RC_BOX.ID) > .from(RC_BOX) > .where(RC_BOX.LC_ASSORTMENT_ID.equal(lcAssortmentId)) > .limit(oldNumberOfBoxesValue - newNumberOfBoxes > ))); > > > The problem is that the Oracle dialect rewrite the query with a help > expression (rownum) that is > not filtered out in the final subselect and the above statement cannot be > executed. > > SQL [delete from STAN1.RC_BOX > where STAN1.RC_BOX.ID in ( > select * from ( > select LIMIT_13865716.*, rownum as ROWNUM_13865716 > from ( > select STAN1.RC_BOX.ID > from STAN1.RC_BOX > where STAN1.RC_BOX.LC_ASSORTMENT_ID = ? > ) LIMIT_13865716 > where rownum <= (? + ?) > ) > where ROWNUM_13865716 > ? > )]; ORA-00913: too many values > > > > As workaround, we tried the following and it seems to work, but it's a bit > ugly. > > .delete(RC_BOX) > .where( > RC_BOX.ID.in(DSL > .select(field("ID", Long.class)) > .from(DSL > .select(RC_BOX.ID) > .from(RC_BOX) > .where(RC_BOX.LC_ASSORTMENT_ID.equal(lcAssortmentId)) > .limit(oldNumberOfBoxesValue - newNumberOfBoxes)) > )); > > > > Has someone else encountered the same problem? > > Cheers. > stan. > > -- > 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.
