Query .list() returning wrong size when used with parameter includeProcessVariables() or includeTaskLocalVariables()

[ilalaina]

Hi all,

I got incorrect results count when using the .list() method on queries with includeProcessVariables() or includeTaskLocalVariables() parameters.
If I don't use .list() but just .count() the result is correct.

I couldn't reproduce the issue in an unit test with new data because I think the issue is related to my existing process variables.

Below are the tests I did :

<sql>SELECT COUNT(DISTINCT PROC_INST_ID_) FROM ACT_RU_EXECUTION; – = 1140</sql>

runtimeService.createProcessInstanceQuery().count(); // Returns 1140 : correct
runtimeService.createProcessInstanceQuery().list().size(); // Returns 1140 : correct
runtimeService.createProcessInstanceQuery().includeProcessVariables().count(); // Returns 1140 : correct
runtimeService.createProcessInstanceQuery().includeProcessVariables().list().size(); // Returns 916 : INCORRECT
‍‍‍‍‍‍

<sql>SELECT COUNT(*) FROM ACT_RU_TASK; – = 1182</sql>

taskService.createTaskQuery().count(); // Returns 1182 : correct
taskService.createTaskQuery().list().size(); // Returns 1182 : correct
taskService.createTaskQuery().includeTaskLocalVariables().count(); // Returns 1182 : correct
taskService.createTaskQuery().includeTaskLocalVariables().list().size(); // Returns 1182 : correct
taskService.createTaskQuery().includeProcessVariables().count(); // Returns 1182 : correct
taskService.createTaskQuery().includeProcessVariables().list().size(); // Returns 917 : INCORRECT
‍‍‍‍‍‍‍‍

<sql>SELECT COUNT(*) FROM ACT_HI_TASKINST; – = 6589</sql>

historyService.createHistoricTaskInstanceQuery().count(); // Returns 6589 : correct
historyService.createHistoricTaskInstanceQuery().list().size(); // Returns 6589 : correct
historyService.createHistoricTaskInstanceQuery().includeTaskLocalVariables().count(); // Returns 6589 : correct
historyService.createHistoricTaskInstanceQuery().includeTaskLocalVariables().list().size(); // Returns 1256 : INCORRECT
historyService.createHistoricTaskInstanceQuery().includeProcessVariables().count(); // Returns 6589 : correct
historyService.createHistoricTaskInstanceQuery().includeProcessVariables().list().size(); // Returns 1256 : INCORRECT
‍‍‍‍‍‍‍‍

Thanks for your your help.

[doganmesut]

Same situation  that we have faced on production system.
Any help will make us glad.

[edsonrichter]

Would you capture the SQL sent to database? It seems to be a "inner join" x "outer join" problem… Capturing the SQL sent to database would clarify that.
In case you use PostgreSQL, you change postgresql.conf enabling

log_min_duration_statement = 0


So, all statements will be recorded in log (be careful to not enable this in production servers!!!)
Order RDBMS would have similar feature to enable statements log (AFAIK, MS SQL and Oracle both have similar feature).

Regards,

Edson

[ilalaina]

I'd like to share the solution I found : it was due to a hardcoded limit in ProcessEngineConfigurationImpl.java :
Line 520:   protected int executionQueryLimit = 20000;
Line 521:   protected int taskQueryLimit = 20000;
Line 522:   protected int historicTaskQueryLimit = 20000;
Line 523:   protected int historicProcessInstancesQueryLimit = 20000;

When fetching process variables a join with the table ACT_RU_VARIABLE is performed before computing, and if you have many variables the results are limited to 20000 rows.
The solution is to increase these limits in the process engine configuration :
<java>
SpringProcessEngineConfiguration config = new SpringProcessEngineConfiguration();
config.setExecutionQueryLimit(LIMIT);
config.setTaskQueryLimit(LIMIT);
config.setHistoricTaskQueryLimit(LIMIT);
config.setHistoricProcessInstancesQueryLimit(LIMIT);
</java>