SQL error using MSSQL as database

[tcheekva]

Running the following code throws an error with MSSQL.  Runs fine with Oracle.  Tested from 5.13 thru 5.16.1

         ProcessEngine processEngine = ProcessEngines.getProcessEngine(dbname);
         RepositoryService repositoryService = processEngine
               .getRepositoryService();

         List<ProcessDefinition> processDefinitions = repositoryService
               .createProcessDefinitionQuery().active().latestVersion()
               .orderByProcessDefinitionName().asc()
               .orderByProcessDefinitionVersion().asc().list();

com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
        at org.activiti.custom.spring.CustomQuery.getResultSet(CustomQuery.java:45)
        at org.activiti.custom.spring.CustomGroupEntityManager.findGroupsByUser(CustomGroupEntityManager.java:57)
        at org.activiti.engine.impl.TaskQueryImpl.getGroupsForCandidateUser(TaskQueryImpl.java:827)
        at org.activiti.engine.impl.TaskQueryImpl.getCandidateGroups(TaskQueryImpl.java:817)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.apache.ibatis.reflection.invoker.MethodInvoker.invoke(MethodInvoker.java:37)
        at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:153)
        at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:44)
        at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:123)
        at org.apache.ibatis.builder.xml.dynamic.DynamicContext$ContextMap.get(DynamicContext.java:89)
        at org.apache.ibatis.builder.xml.dynamic.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:107)
        at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:1657)
        at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:92)
        at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:170)
        at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:210)
        at org.apache.ibatis.ognl.ASTNotEq.getValueBody(ASTNotEq.java:49)
        at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:170)
        at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:210)
        at org.apache.ibatis.ognl.ASTAnd.getValueBody(ASTAnd.java:56)
        at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:170)
        at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:210)
        at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:333)
        at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:413)
        at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:395)
        at org.apache.ibatis.builder.xml.dynamic.OgnlCache.getValue(OgnlCache.java:42)
        at org.apache.ibatis.builder.xml.dynamic.ExpressionEvaluator.evaluateBoolean(ExpressionEvaluator.java:32)
        at org.apache.ibatis.builder.xml.dynamic.IfSqlNode.apply(IfSqlNode.java:30)
        at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)
        at org.apache.ibatis.builder.xml.dynamic.IfSqlNode.apply(IfSqlNode.java:31)
        at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)
        at org.apache.ibatis.builder.xml.dynamic.TrimSqlNode.apply(TrimSqlNode.java:42)
        at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)
        at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)
        at org.apache.ibatis.builder.xml.dynamic.MixedSqlNode.apply(MixedSqlNode.java:29)
        at org.apache.ibatis.builder.xml.dynamic.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:37)
        at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:241)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:79)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
        at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:416)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:407)
        at org.activiti.engine.impl.persistence.entity.TaskEntityManager.findTasksByQueryCriteria(TaskEntityManager.java:121)
        at org.activiti.engine.impl.TaskQueryImpl.executeList(TaskQueryImpl.java:934)
        at org.activiti.engine.impl.AbstractQuery.execute(AbstractQuery.java:139)
        at org.activiti.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:57)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
        at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
        at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
        at org.activiti.engine.impl.AbstractQuery.list(AbstractQuery.java:113)
        at com.xxx.workflow.Activiti.listUserTasks(Activiti.java:323)

[jbarrez]

Could you create a basic unit test that demonstrates this problem (I see for example CustomGroupEntityManager in the stacktrace) - so we can include this test in our multi-database QA?