Summary

When an instance is connected to MSSQL database and the /rest/usermanagement/1/user/group/nested?username API endpoint is requested, an Offset not supported or Incorrect syntax near 'offset' error will be returned

Environment

• Microsoft SQL Server database

Steps to Reproduce

1. Install JIRA connecting to MSSQL database
2. Create an admin user with username of admin
3. Configure JIRA as a user server in /secure/admin/ConfigureCrowdServer.jspa
   • Remember the application name and password created. I used jira/jira in this example
4. Run a GET request against /rest/usermanagement/1/user/group/nested?username=admin
   • Authentication for this request is the application name and password configured (ie. jira/jira)
   • Username defined in username=admin is an existing user in the instance

Expected Results

Group memberships of the user is returned. For example:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<groups expand="group">
    <group name="jira-administrators">
        <link href="http://localhost:12063/jira730/rest/usermanagement/1/group?groupname=jira-administrators" rel="self"/>
    </group>
    <group name="jira-developers">
        <link href="http://localhost:12063/jira730/rest/usermanagement/1/group?groupname=jira-developers" rel="self"/>
    </group>
    <group name="jira-users">
        <link href="http://localhost:12063/jira730/rest/usermanagement/1/group?groupname=jira-users" rel="self"/>
    </group>
</groups>

Actual Results

• The below exception is returned when tested in JIRA 7.5.0, and also thrown in the atlassian-jira.log file:

  2017-10-27 17:19:15,309 http-nio-16619-exec-11 ERROR anonymous 1039x242x1 gr1s5w 10.60.2.228 /rest/usermanagement/1/user/group/nested [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: Caught SQLS$
      from dbo.cwd_membership MEMBERSHIP
      where MEMBERSHIP.directory_id = ? and MEMBERSHIP.membership_type = ? and MEMBERSHIP.lower_child_name = ?
      order by MEMBERSHIP.lower_parent_name asc
      offset ? rows fetch next ? rows only
  com.querydsl.core.QueryException: Caught SQLServerException for select distinct MEMBERSHIP.lower_parent_name
  from dbo.cwd_membership MEMBERSHIP
  where MEMBERSHIP.directory_id = ? and MEMBERSHIP.membership_type = ? and MEMBERSHIP.lower_child_name = ?
  order by MEMBERSHIP.lower_parent_name asc
  offset ? rows fetch next ? rows only
          at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
          at com.querydsl.sql.Configuration.translate(Configuration.java:459)
          at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
          at com.atlassian.jira.crowd.embedded.ofbiz.OfBizInternalMembershipDao.lambda$findMemberships$6(OfBizInternalMembershipDao.java:438)
          at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:68)
          at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:99)
          at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:68)
          at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:94)
          at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:67)
          at com.atlassian.jira.crowd.embedded.ofbiz.OfBizInternalMembershipDao.findMemberships(OfBizInternalMembershipDao.java:427)
          at com.atlassian.jira.crowd.embedded.ofbiz.OfBizInternalMembershipDao.search(OfBizInternalMembershipDao.java:353)
          at com.atlassian.jira.crowd.embedded.ofbiz.OfBizDelegatingMembershipDao.search(OfBizDelegatingMembershipDao.java:116)
          at com.atlassian.crowd.directory.AbstractInternalDirectory.searchGroupRelationships(AbstractInternalDirectory.java:992)
          at com.atlassian.crowd.manager.directory.RemoteDirectorySearcher.searchDirectGroupRelationships(RemoteDirectorySearcher.java:78)
          at com.atlassian.crowd.manager.directory.RemoteDirectorySearcher.searchNestedGroupRelationships(RemoteDirectorySearcher.java:192)
          at com.atlassian.crowd.manager.directory.DirectoryManagerGeneric.searchNestedGroupRelationships(DirectoryManagerGeneric.java:917)
          at com.atlassian.crowd.manager.application.SingleDirectorySearchStrategy.searchNestedGroupRelationships(SingleDirectorySearchStrategy.java:93)
          at com.atlassian.crowd.manager.application.ApplicationServiceGeneric.searchNestedGroupRelationships(ApplicationServiceGeneric.java:2280)
          ... 3 filtered
          at java.lang.reflect.Method.invoke(Method.java:498)
          at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
          at com.sun.proxy.$Proxy27.searchNestedGroupRelationships(Unknown Source)
          ... 3 filtered
          at java.lang.reflect.Method.invoke(Method.java:498)
          at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
          at com.sun.proxy.$Proxy27.searchNestedGroupRelationships(Unknown Source)
          ... 3 filtered
          at java.lang.reflect.Method.invoke(Method.java:498)
          at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
          at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
          at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
          at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
          at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
          at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
          at com.sun.proxy.$Proxy876.searchNestedGroupRelationships(Unknown Source)
          at com.atlassian.crowd.plugin.rest.service.controller.UsersController.getNestedGroups(UsersController.java:470)
          at com.atlassian.crowd.plugin.rest.service.resource.usermanagement.UsersResource.getNestedGroups(UsersResource.java:559)
          ... 3 filtered
          at java.lang.reflect.Method.invoke(Method.java:498)
          ... 19 filtered
          at com.atlassian.plugins.rest.module.RestDelegatingServletFilter$JerseyOsgiServletContainer.doFilter(RestDelegatingServletFilter.java:154)
          ... 1 filtered
          at com.atlassian.plugins.rest.module.RestDelegatingServletFilter.doFilter(RestDelegatingServletFilter.java:68)
          ... 41 filtered
          at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
          ... 53 filtered
          at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:66)
          ... 1 filtered
          at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:64)
          ... 16 filtered
          at com.atlassian.plugins.rest.module.servlet.RestSeraphFilter.doFilter(RestSeraphFilter.java:37)
          ... 3 filtered
          at com.atlassian.crowd.plugin.rest.filter.SeraphDisablerFilter.doFilter(SeraphDisablerFilter.java:33)
          ... 3 filtered
          at com.atlassian.crowd.plugin.rest.filter.BasicApplicationAuthenticationFilter.doFilter(BasicApplicationAuthenticationFilter.java:97)
          ... 19 filtered
          at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)
          ... 10 filtered
          at com.atlassian.crowd.plugin.rest.filter.RestServiceVersionFilter.doFilter(RestServiceVersionFilter.java:86)
          ... 3 filtered
          at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
          ... 4 filtered
          at com.atlassian.web.servlet.plugin.LocationCleanerFilter.doFilter(LocationCleanerFilter.java:36)
          ... 26 filtered
          at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
          ... 23 filtered
          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
          at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
          at java.lang.Thread.run(Thread.java:748)
  Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'offset'.
          at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
          at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
          at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
          at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
          at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331)
          at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
          at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
          at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446)
          ... 287 more

• The below exception is returned when tested in JIRA 7.3.0, and also thrown in the atlassian-jira.log file:

  2017-10-27 16:59:52,488 http-nio-17730-exec-7 ERROR anonymous 1019x730x1 1y08kwo 10.60.2.228 /rest/usermanagement/1/user/group/nested [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: offset not $
  java.lang.IllegalStateException: offset not supported
          at com.querydsl.sql.SQLServerTemplates.serialize(SQLServerTemplates.java:232)
          at com.querydsl.sql.SQLSerializer.serialize(SQLSerializer.java:212)
          at com.querydsl.sql.ProjectableSQLQuery.serialize(ProjectableSQLQuery.java:465)
          at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:429)
          at com.atlassian.jira.crowd.embedded.ofbiz.OfBizInternalMembershipDao.lambda$findMemberships$6(OfBizInternalMembershipDao.java:438)
          at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:66)
          at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:98)
          at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:67)
          at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:93)
          at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:65)
          at com.atlassian.jira.crowd.embedded.ofbiz.OfBizInternalMembershipDao.findMemberships(OfBizInternalMembershipDao.java:427)
          at com.atlassian.jira.crowd.embedded.ofbiz.OfBizInternalMembershipDao.search(OfBizInternalMembershipDao.java:353)
          at com.atlassian.jira.crowd.embedded.ofbiz.OfBizDelegatingMembershipDao.search(OfBizDelegatingMembershipDao.java:116)
          at com.atlassian.crowd.directory.AbstractInternalDirectory.searchGroupRelationships(AbstractInternalDirectory.java:992)
          at com.atlassian.crowd.manager.directory.RemoteDirectorySearcher.searchDirectGroupRelationships(RemoteDirectorySearcher.java:78)
          at com.atlassian.crowd.manager.directory.RemoteDirectorySearcher.searchNestedGroupRelationships(RemoteDirectorySearcher.java:192)
          at com.atlassian.crowd.manager.directory.DirectoryManagerGeneric.searchNestedGroupRelationships(DirectoryManagerGeneric.java:917)
          at com.atlassian.crowd.manager.application.SingleDirectorySearchStrategy.searchNestedGroupRelationships(SingleDirectorySearchStrategy.java:93)
          at com.atlassian.crowd.manager.application.ApplicationServiceGeneric.searchNestedGroupRelationships(ApplicationServiceGeneric.java:2280)
          ... 3 filtered
          at java.lang.reflect.Method.invoke(Method.java:498)
          at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
          at com.sun.proxy.$Proxy611.searchNestedGroupRelationships(Unknown Source)
          ... 3 filtered
          at java.lang.reflect.Method.invoke(Method.java:498)
          at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
          at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
          at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
          at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
          at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
          at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
          at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
          at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
          at com.sun.proxy.$Proxy3364.searchNestedGroupRelationships(Unknown Source)
          at com.atlassian.crowd.plugin.rest.service.controller.UsersController.getNestedGroups(UsersController.java:470)
          at com.atlassian.crowd.plugin.rest.service.resource.usermanagement.UsersResource.getNestedGroups(UsersResource.java:559)
          ... 3 filtered
          at java.lang.reflect.Method.invoke(Method.java:498)
          ... 19 filtered
          at com.atlassian.plugins.rest.module.RestDelegatingServletFilter$JerseyOsgiServletContainer.doFilter(RestDelegatingServletFilter.java:154)
          ... 1 filtered
          at com.atlassian.plugins.rest.module.RestDelegatingServletFilter.doFilter(RestDelegatingServletFilter.java:68)
          ... 36 filtered
          at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
          ... 53 filtered
          at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:76)
          ... 1 filtered
          at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:74)
          ... 16 filtered
          at com.atlassian.plugins.rest.module.servlet.RestSeraphFilter.doFilter(RestSeraphFilter.java:37)
          ... 3 filtered
          at com.atlassian.crowd.plugin.rest.filter.SeraphDisablerFilter.doFilter(SeraphDisablerFilter.java:33)
          ... 3 filtered
          at com.atlassian.crowd.plugin.rest.filter.BasicApplicationAuthenticationFilter.doFilter(BasicApplicationAuthenticationFilter.java:97)
          ... 20 filtered
          at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)
          ... 10 filtered
          at com.atlassian.crowd.plugin.rest.filter.RestServiceVersionFilter.doFilter(RestServiceVersionFilter.java:86)
          ... 3 filtered
          at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
          ... 4 filtered
          at com.atlassian.web.servlet.plugin.LocationCleanerFilter.doFilter(LocationCleanerFilter.java:36)
          ... 29 filtered
          at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
          ... 28 filtered
          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
          at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
          at java.lang.Thread.run(Thread.java:748)
  

Notes

Doesn't happen when tested with PostgreSQL

Workaround

No known workaround