Hi Stan, Thanks for reporting. This is indeed a bit unfortunate. The SELECT * was an attempt to avoid issues with unnamed columns in your original SELECT. For instance, it would be hard to emulate LIMIT .. OFFSET for something like this:
DSL.select(val(1)) .from(TABLE) .limit(1) .offset(1); Unfortunately, the "*" is simply wrong, because it doesn't remove the synthetic ROWNUM column. This will be fixed thoroughly in jOOQ 3.4 in the context of another problem (https://github.com/jOOQ/jOOQ/issues/2335), e.g. when you have two columns with the same name, using the * won't work either: DSL.select(TABLE1.X, TABLE2.X) .from(TABLE1, TABLE2) .limit(1) .offset(1); #2335 will not be merged to jOOQ 3.2.5, as it depends on a couple of newer features in the jOOQ API (related to derived column lists and improved QueryPart traversal). I think, however, that it would still be possible to inspect the your query's SELECT field list, and to extract field names in order to render them explicitly. I have created #3195 for this https://github.com/jOOQ/jOOQ/issues/3195 (with merge tickets #3196 for jOOQ 3.3 and #3197 for jOOQ 3.2). Thanks again for the detailed report, ... and happy Easter! 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.
