Thanks for reporting this. This is likely a bug in the SQL Server specific implementation of the OFFSET clause. I have registered #2136 for this: https://github.com/jOOQ/jOOQ/issues/2136
As a workaround, try explicitly selecting all relevant columns from [mwp_widget] and [mwp_widget2cat], possibly renaming one (or both) [MUN_ID] columns Cheers Lukas 2013/1/29 Mike Fayer <[email protected]> > Hi all, > > I'm new to jOOQ so apologies if this is a newbie error... > > I'm coding against sql server 2008 with jooq and am using limit/offset for > pagination. When the query is executed without a join, everything works > just fine. With a join, if the offset == 0, the query works. However, if > the offset > 0 and there is a join, I get an exception. The limit seems to > have no effect on the situation. > > The error: > SEVERE: Servlet.service() for servlet [default] in context with path > [/server] threw exception > org.jooq.exception.DataAccessException: SQL > [ > select * from (select limit_107798178.*, row_number() over (order by > [MUN_NAME] asc) > as rownum_107798178 > > from (select top 100 percent [dbo].[mwp_widget].[MUN_ID], > [dbo].[mwp_widget].[MUN_NAME], [dbo].[mwp_widget].[MUN_NUMBER], > [dbo].[mwp_widget2cat].[MUN_ID], [dbo].[mwp_widget2cat].[RCA_ID] > > from [dbo].[mwp_widget] join [dbo].[mwp_widget2cat] > on [dbo].[mwp_widget].[MUN_ID] = [dbo].[mwp_widget2cat].[MUN_ID] > > where ([dbo].[mwp_widget].[MUN_NUMBER] is not null and > [dbo].[mwp_widget].[MUN_NAMEID] like ? and ([dbo].[mwp_widget2cat].[RCA_ID] > = ? or [dbo].[mwp_widget2cat].[RCA_ID] = ?)) order by > [dbo].[mwp_widget].[MUN_NAME] asc) as limit_107798178) as > outer_limit_107798178 where rownum_107798178 > ? and rownum_107798178 <= (? > + ?) > ]; > > The column 'MUN_ID' was specified multiple times for 'limit_107798178'. > > ---- > > Thanks in advance! > > Mike > > -- > 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/groups/opt_out. > > > -- 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/groups/opt_out.
