Historic process instances query withIncidents combined with orQueries provides incorrect results

[Bernd-Focke-MT]

Environment

Camunda 7.18

Description

REST API for (historic) process instances (/rest/history/prpocess-instance) provide incorrect results in some specific cases:

When querying historic process instances with the filter " withIncidents" AND “orQueries” (processVariableA == “value1” OR processVariableA == “value2” )" incorrect result is provided.

Steps to reproduce

Create a request querying historic process instances with the filter " withIncidents" AND “orQueries” (processVariableA == “value1” OR processVariableA == “value2” )" like the following:

Observed Behavior

The search result contains wrong results: e.g. process instances with incidents but also process instances without incidents.

Expected behavior

The search result should contain process instances with incidents only AND with one of the given variables.

Root Cause

We have identified, that the SQL Query is created with “left join” instead of an “inner join”:

In case withIncidents is part of the conjunction, an inner join is required. In case withIncidents is part of the OR query, a left join is still required.

Solution Ideas

• Consider adjusting JOIN_TYPE only incident related filter is part of orQuery, e.g.:

    <bind name="JOIN_TYPE" value="'inner join'" />

    <foreach collection="queries" item="query">

      <if test="query != null &amp;&amp; (query.withIncidents || query.withRootIncidents || query.incidentStatus != null || query.incidentMessage != null || query.incidentMessageLike != null || query.incidentType != null)">
        <bind name="INC_JOIN" value="true" />
        <if test="query.isOrQueryActive">
          <bind name="JOIN_TYPE" value="'left join'" />
        </if>
      </if>
      ...
</foreach>

This will work if incident related filter is either with AND or in OR query. Also the JOIN_TYPE is affecting only this table. However, we need to consider other scenario and check what's the state of Task orQuery.

Hints

Links

• test: 49a82bf

Breakdown

Pull requests

Beta Give feedback

1. Fix get historic process instance api orquery for mutiple statues and… #4515
   ci:all-db ci:default-build ci:migration ci:rest-api +4
   
2. Fix get historic process instance api orquery for mutiple statues and withIncidents combined with orQuery #4529
   ci:all-db ci:default-build ci:migration ci:rest-api +4
   

Dev2QA handover

• Does this ticket need a QA test and the testing goals are not clear from the description? Add a Dev2QA handover comment

[yanavasileva]

Hello @Bernd-Focke-MT ,

Thank you for reaching out to us with this.
I will have a look at the report and get back to you with feedback.
Please note there might be some delay in the processing of this due to team other responsibilities.
In order to speed up the process, you can consider raising a pull request to reproduce and potentially fix the issue. You can find here a link to test cases (HistoricProcessInstanceQueryOrTest) and link to the query HistoricProcessInstance.xml#L275.

Best regards,
Yana

[yanavasileva]

Hello @Bernd-Focke-MT,

I was able to reproduce and confirm the bug. Thanks for the heads up about the root cause being the different JOIN with the Incidents table.
As next step, I will send the ticket for a decision, then make it transparent here.

Best,
Yana

[yanavasileva]

Hello @Bernd-Focke-MT,

We will not be able to work on this any time soon. Let us know if you consider to make a code contribution.
Thank you for your understanding.

Best regards,
Yana

[kmannuru]

Hello, I'm working on fixing this issue. Along with this fix, I'll also be pushing changes to the related issue to the same PR.
Related issue : https://forum.camunda.io/t/rest-orqueries-historic-process-instance-statuses/26518/2
Please let me know if any concerns.

[yanavasileva]

Hello @kmannuru,

It's great that you want to make a contribution on this.
Here are some considerations:

• Keep in mind query performance when adjusting the historic process instance query
• We need good test coverage as the reported issue might get fixes but other scenarios could break along the way. I created two test cases when the issue was reported, you can find them here: test(engine): test withIncidents with process instance orQuery #3239
• The solution should work for all supported databases by Camunda 7, we can help you to validate that by triggering our CI.

Best,
Yana