Camunda and H2. Getting exception while try to execute HistoryProcessInstanceQuery

Camunda 7 Topics Discussion & Questions
1

Hello everyone!

I want to get last completed process by business key via HistoricProcessInstanceQuery. For that, I create method, that configure the query like below:

private HistoricProcessInstanceQuery buildCompletedProcessHistoryQuery(EntityType entityType, Long businessKey, String processDefinitionKey) {
        ProcessEngine processEngine = BpmPlatform.getDefaultProcessEngine();
        RepositoryService repositoryService = processEngine.getRepositoryService();

        ProcessDefinition processDefinition = repositoryService.createProcessDefinitionQuery()
                .processDefinitionKey(processDefinitionKey)
                .latestVersion()
                .singleResult();

        return historyService.createHistoricProcessInstanceQuery()
                .finished()
                .processDefinitionId(processDefinition.getId())
                .processInstanceBusinessKey(businessKey.toString())
                .variableValueEquals(ENTITY_TYPE, entityType.toString())
                .orderByProcessInstanceEndTime().desc();
    }

After calling on result of this method .list() or .listPage() I get an exception:

org.h2.jdbc.JdbcSQLException: Order by expression "RES.END_TIME_" must be in the result list in this case; SQL statement:
select distinct RES.ID_
    from (

    SELECT SELF.*, DEF.NAME_, DEF.VERSION_
    FROM ACT_HI_PROCINST SELF

    LEFT JOIN ACT_RE_PROCDEF DEF
    ON SELF.PROC_DEF_ID_ = DEF.ID_

     WHERE  (
        1 = 1 and
           SELF.PROC_DEF_ID_ = ?
       
              and SELF.BUSINESS_KEY_ = ?

              and SELF.END_TIME_ is not NULL
                 
              and EXISTS (
              select
              ID_
              from
              ACT_HI_VARINST
              WHERE
        NAME_= ?
              AND PROC_INST_ID_ = SELF.PROC_INST_ID_           
                and 
    (
    
      (  VAR_TYPE_ is not null and VAR_TYPE_ = ?
       
          and TEXT_ is not null and 
		  TEXT_  
    =
		  ? )

    )
               )
        ) 

    ) RES
    order by RES.END_TIME_ desc
    LIMIT ? OFFSET ? [90068-196]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.command.dml.Query.initOrder(Query.java:482) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.command.dml.Select.init(Select.java:750) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.command.Parser.parseSelect(Parser.java:1749) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.command.Parser.parsePrepared(Parser.java:449) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.command.Parser.parse(Parser.java:321) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.command.Parser.parse(Parser.java:293) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.command.Parser.prepareCommand(Parser.java:258) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.engine.Session.prepareLocal(Session.java:578) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.engine.Session.prepareCommand(Session.java:519) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73) ~[h2-1.4.196.jar:1.4.196]
	at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288) ~[h2-1.4.196.jar:1.4.196]
	at sun.reflect.GeneratedMethodAccessor51.invoke(Unknown Source) ~[na:na]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_251]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_251]
	at com.atomikos.jdbc.AtomikosConnectionProxy.invoke(AtomikosConnectionProxy.java:141) ~[transactions-jdbc-4.0.6.jar:na]
	at com.sun.proxy.$Proxy111.prepareStatement(Unknown Source) [na:na]
	at sun.reflect.GeneratedMethodAccessor51.invoke(Unknown Source) ~[na:na]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_251]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_251]
	at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:241) [spring-jdbc-5.2.2.RELEASE.jar:5.2.2.RELEASE]
	at com.sun.proxy.$Proxy112.prepareStatement(Unknown Source) [na:na]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:87) [mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88) [mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59) [mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.BatchExecutor.doQuery(BatchExecutor.java:91) [mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) [mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) [mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) [mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) [mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) [mybatis-3.4.4.jar:3.4.4]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) [mybatis-3.4.4.jar:3.4.4]
	at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:97) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectListWithRawParameter(DbEntityManager.java:181) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:173) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.persistence.entity.HistoricProcessInstanceManager.findHistoricProcessInstanceIds(HistoricProcessInstanceManager.java:135) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.HistoricProcessInstanceQueryImpl.executeIdsList(HistoricProcessInstanceQueryImpl.java:384) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.AbstractQuery.evaluateExpressionsAndExecuteIdsList(AbstractQuery.java:369) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.AbstractQuery.execute(AbstractQuery.java:198) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:28) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:110) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:46) [camunda-engine-spring-7.12.0.jar:7.12.0]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) [spring-tx-5.2.2.RELEASE.jar:5.2.2.RELEASE]
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:44) [camunda-engine-spring-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.AbstractQuery.listIds(AbstractQuery.java:354) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.cmd.AbstractRestartProcessInstanceCmd.collectProcessInstanceIds(AbstractRestartProcessInstanceCmd.java:58) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.cmd.RestartProcessInstancesCmd.execute(RestartProcessInstancesCmd.java:68) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.cmd.RestartProcessInstancesCmd.execute(RestartProcessInstancesCmd.java:55) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:28) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:110) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:46) [camunda-engine-spring-7.12.0.jar:7.12.0]
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) [spring-tx-5.2.2.RELEASE.jar:5.2.2.RELEASE]
	at org.camunda.bpm.engine.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:44) [camunda-engine-spring-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:70) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.RestartProcessInstanceBuilderImpl.execute(RestartProcessInstanceBuilderImpl.java:86) [camunda-engine-7.12.0.jar:7.12.0]
	at org.camunda.bpm.engine.impl.RestartProcessInstanceBuilderImpl.execute(RestartProcessInstanceBuilderImpl.java:82) [camunda-engine-7.12.0.jar:7.12.0]

Is that Сamunda and H2 compatibility problem or I did something wrong?

Without .orderByProcessInstanceEndTime().desc() that query works fine.

1 Like
2

@bebs if the process instance is not completed,then end time won’t be available for that instance.

Will work for completed process instances.

3

Now, I tried with .completed() instead of .finished() and got same exception.

If the problem is that there is endTime not availible as you say, then this means that I can filter by other field (e.g. InstanceId). But with .orderByProcessInstanceId().desc I am getting similiar exception.

4