Issue with TaskQuery involving CandidateGroup and Assignee

I’m using an old version of Camunda (7.9.0) and have found an issue with a task query that I am running.

In simplistic terms, I’m trying to say:

“Return me all the Tasks where the Candidate Group is X, a Process Variable is equal to Y and the task is either Unassigned or Assigned to Z”

The java code is as follows:

    TaskQuery taskQuery = taskService.createTaskQuery()
                                     .processInstanceBusinessKey(businessKey);

    if (candidateGroup != null)
    {
        switch (candidateGroup)
        {
            case SUPPLIER:
            case CUSTOMER:
                taskQuery = taskQuery.taskCandidateGroup(candidateGroup.name())
                                     .processVariableValueEquals(candidateGroup.getCandidateIdVar(), safeId(candidateIdFilter));
                break;
            default:
                break;
        }
    }

    taskQuery = taskQuery.or().taskUnassigned().taskAssignee(assignee).endOr();

    taskQuery = taskQuery.initializeFormKeys()
                         .active()
                         .orderByDueDate().asc();

    List<Task> tasks = taskQuery.listPage(pageRequest.getPageNumber() * pageRequest.getPageSize(), pageRequest.getPageSize());

This query currently returns me no tasks, when given the criteria provided it should return me a record with the test data I have.

I believe this is because the candidate part of the SQL generated is adding criteria in about the assigneed being NULL.

For example, this is the SQL generated from the code above:

select distinct RES.REV_, RES.ID_, RES.NAME_, RES.PARENT_TASK_ID_, RES.DESCRIPTION_, RES.PRIORITY_,
RES.CREATE_TIME_, RES.OWNER_, RES.ASSIGNEE_, RES.DELEGATION_, RES.EXECUTION_ID_, RES.PROC_INST_ID_,
RES.PROC_DEF_ID_, RES.CASE_EXECUTION_ID_, RES.CASE_INST_ID_, RES.CASE_DEF_ID_, RES.TASK_DEF_KEY_,
RES.DUE_DATE_, RES.FOLLOW_UP_DATE_, RES.SUSPENSION_STATE_, RES.TENANT_ID_ from ACT_RU_TASK RES
inner join ACT_RU_IDENTITYLINK I on I.TASK_ID_ = RES.ID_
inner join ACT_RU_EXECUTION E on RES.PROC_INST_ID_ = E.ID_
WHERE ( 1 = 1 and E.BUSINESS_KEY_ = ‘48fd8613-cd0f-494d-a949-a990c4f3fac7’ and ( RES.ASSIGNEE_ is null and
I.TYPE_ = ‘candidate’ and ( I.GROUP_ID_ IN ( ‘SUPPLIER’ ) ) ) and EXISTS
( select ID_ from ACT_RU_VARIABLE WHERE NAME_= ‘supplierId’ and TASK_ID_ is null
and RES.PROC_INST_ID_ = PROC_INST_ID_ and ( ( TYPE_ is not null and TYPE_ = ‘string’
and TEXT_ is not null and TEXT_ = ‘34ad0f8e-c99d-11ea-87d0-0242ac130003’ ) ) ) and RES.SUSPENSION_STATE_ = 1 )
and ( 1 = 1 and RES.ASSIGNEE_ = ‘904e83c6-3366-4992-86fa-515fcc4fabe0’ or RES.ASSIGNEE_ IS NULL )
order by RES.DUE_DATE_ asc LIMIT 10 OFFSET 0

Please note the “RES.ASSIGNEE_ is null and” part just before the ‘candidate’ expression.

If I remove this from the SQL and re-run it, then it returns me the record that I expected.

So a few questions:

  1. Is this a bug fixed in later versions (I appreciate we are on a very old version)
  2. Is there a better way to construct the query that will fix this in my current version (7.9.0)

Thanks Steve