topic Query .list() returning wrong size when used with parameter includeProcessVariables() or includeTaskLocalVariables() in Alfresco Archive

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

ilalaina — Thu, 04 Aug 2016 08:54:29 GMT

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

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

doganmesut — Fri, 05 Aug 2016 12:19:42 GMT

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

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

edsonrichter — Sat, 06 Aug 2016 20:14:29 GMT

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

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

ilalaina — Fri, 12 Aug 2016 08:20:18 GMT

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>