Hi Lukas, This is a great update :) the queries are fine and the connection leak is away :))) Thx.
cheers. stan. On Tuesday, July 15, 2014 5:20:43 PM UTC+2, Lukas Eder wrote: > > Hi Stan, > > I believe that you might be interested in the recently published release > jOOQ 3.2.6, which backports the fix for > - https://github.com/jOOQ/jOOQ/issues/2335 > - https://github.com/jOOQ/jOOQ/issues/3195 > > LIMIT .. OFFSET emulation in Oracle is now even more precise > > Let me know how your upgrade goes. > > Cheers > Lukas > > > 2014-06-08 11:46 GMT+02:00 Stanislas Nanchen <[email protected] > <javascript:>>: > >> Hi Lukas, >> >> This is awesome :) Thank you for fixing all that. >> cheers! >> stan. >> >> On Wednesday, June 4, 2014 5:38:52 PM UTC+2, Lukas Eder wrote: >> >>> 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] <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 [email protected]. For more options, visit https://groups.google.com/d/optout.
