Task Query limitation

[sebf]

Hello,

If a user has more than 1000 groups, ibatis select task query, not working.
Indeed,
          <if test="candidateGroups != null &amp;&amp; candidateGroups.size() &gt; 0">
            I.GROUP_ID_ IN
            <foreach item="group" index="index" collection="candidateGroups"
                     open="(" separator="," close=")">
              #{group}
            </foreach>
          </if>

Sample with Oracle :
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

### The error may exist in org/activiti/db/mapping/entity/Task.xml
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.selectTaskByQueryCriteria-Inline
### The error occurred while setting parameters
### SQL: select * from ( select a.*, ROWNUM rnum from (     select distinct RES.*              from ACT_RU_TASK RES                  
inner        join ACT_RU_IDENTITYLINK I on I.TASK_ID_ = RES.ID_                              
inner join ACT_RU_VARIABLE A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_                                           
inner join ACT_RU_VARIABLE A1 on RES.PROC_INST_ID_ = A1.PROC_INST_ID_                                          
inner join ACT_RU_VARIABLE A2 on RES.PROC_INST_ID_ = A2.PROC_INST_ID_                                               
inner join ACT_RE_PROCDEF D on RES.PROC_DEF_ID_ = D.ID_                       
WHERE  RES.TASK_DEF_KEY_ = ?                                    
and D.KEY_ = ?                                                                                                                               
and RES.ASSIGNEE_ is null        
and I.TYPE_ = 'candidate'        
and          (                         I.USER_ID_ = ?                                           
or                                   I.GROUP_ID_ IN             (               ?             ,               ?          , ………………   ,               ?             ,               ?             ,               ?             )
                   ) 

  and A0.TASK_ID_ is null and A0.NAME_= ? and A0.TYPE_ = ? and A0.LONG_   = ?
and A1.TASK_ID_ is null and A1.NAME_= ? and A1.TYPE_ = ? and A1.LONG_   = ? and A2.TASK_ID_ is null and A2.NAME_= ? and A2.TYPE_ = ? and A2.TEXT_ =   ?              
order by RES.ID_ asc        ) a where ROWNUM < ?) where rnum  >= ?
### Cause: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
   at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:426)
   at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:417)
   at org.activiti.engine.impl.persistence.entity.TaskEntityManager.findTasksByQueryCriteria(TaskEntityManager.java:135)


Is it possible to split SELECT IN query to avoid this mistake ?

Thanks in advance.

[jbarrez]

Hmm this is the first time in the five years of Activiti that I see this error.

I'm not sure what a quick fix for this would be (except completely rewriting). Are you passing the groups all in the query or is this done automatically?

[sebf]

This request was executed automatically by the .taskCandidateUser(userId) critieria.

[trademak]

This is a limitation of Oracle IN queries, so there's not much we can do about this specific query. You could use your own NativeTaskQuery to prevent this specific issue.

Best regards,