SQL exception with Task query after upgrade to 6.7.0

Flowable Engine
1

This code snippet:

TaskQuery query = engine.getTaskService().createTaskQuery().taskWithFormKey();
query.processInstanceBusinessKeyLike(FlowableRuntimeUtils.getProcBusinessKey("%", userKey));
query.taskCandidateOrAssigned(authUser);
query.listPage(page, size);

is throwing the following exception (works with 6.6.0):

### The error may exist in org/flowable/task/service/db/mapping/entity/Task.xml
### The error may involve org.flowable.task.service.impl.persistence.entity.TaskEntityImpl.selectTaskByQueryCriteria
### The error occurred while executing a query
### SQL: SELECT RES.*                from ACT_RU_TASK RES            WHERE  exists (             select 1             from ACT_RU_EXECUTION E             WHERE RES.PROC_INST_ID_ = E.ID_                                                       and E.BUSINESS_KEY_ like ?           )                                                                                                                                                                                               and RES.FORM_KEY_ IS NOT NULL                                                                                                                                                                       and (RES.ASSIGNEE_ = ?               or (                                RES.ASSIGNEE_ is null and                                exists(select LINK.ID_ from ACT_RU_IDENTITYLINK LINK where LINK.TASK_ID_ = RES.ID_                 and LINK.TYPE_ = 'candidate' and (LINK.USER_ID_ = ? or                                  ))))                order by RES.ID_ asc        LIMIT ? OFFSET ?
### Cause: org.apache.openjpa.lib.jdbc.ReportingSQLException: Syntax error in SQL statement "SELECT RES.*

The reason seems to be the missing clause after this or:

and LINK.TYPE_ = 'candidate' and (LINK.USER_ID_ = ? or                                  )

Any hint?

2

I believe the error is here: flowable-engine/Task.xml at flowable-6.7.0 · flowable/flowable-engine · GitHub e.g. with unconditional or.

3

Here is the PR: Fixing empty or condition when there are no candidateGroups by ilgrosso · Pull Request #3035 · flowable/flowable

4

You are right @ilgrosso thanks a lot for the PR, we’ll integrate it shortly.

For the time being you can adjust your query a bit to:

TaskQuery query = engine.getTaskService().createTaskQuery().taskWithFormKey();
query.processInstanceBusinessKeyLike(FlowableRuntimeUtils.getProcBusinessKey("%", userKey));
query.or().taskCandidateUser(authUser).taskAssignee(authUser).endOr();
query.listPage(page, size);

Doing this will generate the following SQL:

SELECT RES.*
from ACT_RU_TASK RES
WHERE exists(select 1 from ACT_RU_EXECUTION E WHERE RES.PROC_INST_ID_ = E.ID_ and E.BUSINESS_KEY_ like ?)
  and RES.FORM_KEY_ IS NOT NULL
  and (RES.ASSIGNEE_ = ? or (RES.ASSIGNEE_ is null and exists(select LINK.ID_
                                                              from ACT_RU_IDENTITYLINK LINK
                                                              where LINK.TYPE_ = 'candidate' and LINK.TASK_ID_ = RES.ID_ and (LINK.USER_ID_ = ?))))
order by RES.ID_ asc 
LIMIT ?
OFFSET ?

and if the user is a member of some groups it will generate the following:

SELECT RES.*
from ACT_RU_TASK RES
WHERE exists(select 1 from ACT_RU_EXECUTION E WHERE RES.PROC_INST_ID_ = E.ID_ and E.BUSINESS_KEY_ like ?)
  and RES.FORM_KEY_ IS NOT NULL
  and (RES.ASSIGNEE_ = ? or (RES.ASSIGNEE_ is null and exists(select LINK.ID_
                                                              from ACT_RU_IDENTITYLINK LINK
                                                              where LINK.TYPE_ = 'candidate'
                                                                and LINK.TASK_ID_ = RES.ID_
                                                                and (LINK.USER_ID_ = ? or (LINK.GROUP_ID_ IN (?, ?))))))
order by RES.ID_ asc
LIMIT ?
OFFSET ?

Which is identical to the taskCandidateOrAssigned call.

Cheers,
Filip

1 Like