using a jobquery with processInstanceId AND executable filter fails with an SQLException

Description

It is not possible to build a Job query using the executable() filter combined with the processInstanceId() filter.

Adding the following test in JobQueryTest shows the problem:

public void testQueryByProcessInstanceIdExecutable() {
JobQuery query = managementService.createJobQuery().processInstanceId(processInstanceIdOne).executable();
verifyQueryResults(query, 1);
}

It triggers a sql exception:

org.apache.ibatis.exceptions.PersistenceException:

  1.  

    1.  

      1. Error querying database. Cause: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT *

FROM ACT_RU_JOB J
WHERE J.PROCESS_INSTANCE_ID_ = ?

(J[*].RETRIES_ > 0)
AND (J.DUEDATE_ IS NULL OR J.DUEDATE_ <= ?) "; SQL statement:
select *

from ACT_RU_JOB J
WHERE J.PROCESS_INSTANCE_ID_ = ?
(J.RETRIES_ > 0)
and (J.DUEDATE_ is null or J.DUEDATE_ <= ?) [42000-132]

  1.  

    1.  

      1. The error may exist in org/activiti/db/mapping/entity/Job.xml

      2. The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectJobByQueryCriteria

      3. The error occurred while executing a query

      4. SQL: select * from ACT_RU_JOB J WHERE J.PROCESS_INSTANCE_ID_ = ? (J.RETRIES_ > 0) and (J.DUEDATE_ is null or J.DUEDATE_ <= ?)

      5. Cause: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT *
        FROM ACT_RU_JOB J
        WHERE J.PROCESS_INSTANCE_ID_ = ?
        (J[*].RETRIES_ > 0)
        AND (J.DUEDATE_ IS NULL OR J.DUEDATE_ <= ?) "; SQL statement:
        select *
        from ACT_RU_JOB J
        WHERE J.PROCESS_INSTANCE_ID_ = ?
        (J.RETRIES_ > 0)
        and (J.DUEDATE_ is null or J.DUEDATE_ <= ?) [42000-132]
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:77)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:69)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:193)
        at org.activiti.engine.impl.persistence.entity.JobManager.findJobsByQueryCriteria(JobManager.java:114)
        at org.activiti.engine.impl.JobQueryImpl.executeList(JobQueryImpl.java:173)
        at org.activiti.engine.impl.AbstractQuery.execute(AbstractQuery.java:138)
        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
        at org.activiti.engine.impl.AbstractQuery.list(AbstractQuery.java:112)
        at org.activiti.engine.test.api.mgmt.JobQueryTest.verifyQueryResults(JobQueryTest.java:319)
        at org.activiti.engine.test.api.mgmt.JobQueryTest.testQueryByProcessInstanceIdExecutable(JobQueryTest.java:167)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at junit.framework.TestCase.runTest(TestCase.java:168)
        at org.activiti.engine.impl.test.PvmTestCase.runTest(PvmTestCase.java:75)
        at junit.framework.TestCase.runBare(TestCase.java:134)
        at org.activiti.engine.impl.test.AbstractActivitiTestCase.runBare(AbstractActivitiTestCase.java:89)
        at junit.framework.TestResult$1.protect(TestResult.java:110)
        at junit.framework.TestResult.runProtected(TestResult.java:128)
        at junit.framework.TestResult.run(TestResult.java:113)
        at junit.framework.TestCase.run(TestCase.java:124)
        at junit.framework.TestSuite.runTest(TestSuite.java:232)
        at junit.framework.TestSuite.run(TestSuite.java:227)
        at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:83)
        at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
        at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
        at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Apparently it can be fixed in org/activiti/db/mapping/entity/Job.xml by adding an "and" at the beginning of the <if test="executable"> in the selectJobByQueryCriteriaSql definition:
<if test="executable">
and (J.RETRIES_ &gt; 0)
and (J.DUEDATE_ is null or J.DUEDATE_ &lt;= #{now, jdbcType=TIMESTAMP})
</if>

Environment

None

Assignee

Unassigned

Reporter

vincent boulaye

Labels

None

Components

Fix versions

Affects versions

Priority

Major
Configure