Hi Witold, After all this time, we have found a fix for #2335: https://github.com/jOOQ/jOOQ/issues/2335
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 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. Cheers, Lukas 2013-03-14 7:35 GMT+01:00 Lukas Eder <[email protected]>: > > 2013/3/14 Witold Szczerba <[email protected]> > >> Hi, >> my test case with aliasing FOLDER.ID.as <http://folder.id.as/>(f_id) and >> SUBJECT.ID.as <http://subject.id.as/>(s_id) works fine, but it is >> impossible to apply that workaround to queries across the system, as we >> have rather complex mappers, most of the time specified elsewhere (some are >> reusable) and those mappers does not know about the aliases used to create >> the query. Also, queries produce like 30-40 columns, so we would have to >> enumerate (and alias) each and every column in every query (most of the >> times we use implicit columns by just not adding any to the queries). All >> that would turn simple and concise queries into huge, hard to maintain >> monsters. >> > > I understand > > >> I was thinking, if JOOQ could automatically alias the TableFields when >> rendering the inner query (or any query). Alias could be a concatenation of >> a table and column, with dot replaced by underscore, example: >> >> select "FOLDER"."ID" FOLDER_ID, "SUBJECT"."ID" SUBJECT_ID >> from "FOLDER" >> > > The problem with that is: How would you recognise those system-generated > aliases? You wouldn't be able to access any data from the result anymore, > except if you access values by index... This gets worse if you start > nesting SELECT statements. Then, jOOQ would "system-rename" the columns > twice. > > The only backward incompatibility I can think of, is when user already >> specified an explicit alias with identical name, but it seems very unlikely. >> > > There is no such thing as "unlikeliness". Everything that can happen, will > happen. A solution to this problem has to be sound, predictable and it has > to work the same way for all databases - including the ones with native > LIMIT .. OFFSET support. > > This needs some more thinking... > > Cheers > 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
