1 Reply Latest reply on Oct 29, 2014 4:25 AM by swiderski.maciej

    ORA-01795: maximum number of expressions in a list is 1000 (proposed fix)

    fgiannetti
    fgiannetti Oct 23, 2014 10:23 AM

      Hi All!

      Im using Oracle Database with my JBPM 6.0.

      After several days with the application online, i get this error in the completed tasks query method:

       

      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: ORA-01795: maximum number of expressions in a list is 100
  at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
  at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1289) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
  at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:261) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
  at org.jbpm.shared.services.impl.JbpmServicesPersistenceManagerImpl.queryWithParameters(JbpmServicesPersistenceManagerImpl.java:541) [jbpm-shared-services-6.0.0.Final.jar:6.0.0.Final]
  at org.jbpm.shared.services.impl.JbpmServicesPersistenceManagerImpl.queryWithParametersInTransaction(JbpmServicesPersistenceManagerImpl.java:446) [jbpm-shared-services-6.0.0.Final.jar:6.0.0.Final]
  ... 116 more

       

      Searching in the code I saw that the problem is in this named query in the Taskorm.xml file:

       

      <named-query name="TasksOwnedPotentialOwnersByTaskIds">
          <query>
  select
      t.id,
      potentialOwners.id
  from TaskImpl t, OrganizationalEntityImpl potentialOwners        
  where t.id in (:taskIds) and
    potentialOwners in elements ( t.peopleAssignments.potentialOwners  )  and      
    t.archived = 0 and
    t.taskData.status in ('Created', 'Ready', 'Reserved', 'InProgress', 'Suspended') 
          </query>
          <!-- hint name="org.hibernate.timeout" value="200"/ -->
 </named-query>

       

      The class that invokes that query is TaskQueryServiceImpl in this way:

       

      public List<TaskSummary> getTasksOwnedByStatus(String userId, List<Status> status, String language) {


        List<TaskSummary> taskOwned = (List<TaskSummary>) pm.queryWithParametersInTransaction("TasksOwnedWithParticularStatus", 
                pm.addParametersToMap("userId", userId, "status", status, "language", language));


        if (!taskOwned.isEmpty()) {
            Set<Long> tasksIds = new HashSet<Long>();
            for (TaskSummary ts : taskOwned) {
                tasksIds.add(ts.getId());
            }


            List<Object[]> tasksPotentialOwners = (List<Object[]>) pm.queryWithParametersInTransaction("TasksOwnedPotentialOwnersByTaskIds",
                        pm.addParametersToMap("taskIds", tasksIds));


            Map<Long, List<String>> potentialOwners = new HashMap<Long, List<String>>();
            for (Object o : tasksPotentialOwners) {
                Object[] get = (Object[]) o;
                tasksIds.add((Long) get[0]);
                if (potentialOwners.get((Long) get[0]) == null) {
                    potentialOwners.put((Long) get[0], new ArrayList<String>());
                }
                potentialOwners.get((Long) get[0]).add((String) get[1]);
            }
            for (TaskSummary ts : taskOwned) {
                ((InternalTaskSummary) ts).setPotentialOwners(potentialOwners.get(ts.getId()));
            }
        } else {
            return new ArrayList<TaskSummary>(0);
        }


        return taskOwned;
    }

       

      Thus, if the taskIds List size id bigger than 1000 elements, Oracle fails.

       

      My fix:

      I've change the named query in the xml like that:

      <named-query name="TasksOwnedPotentialOwnersByTaskIdsNO1000LIMIT">
          <query>
  select
  t.id,
  potentialOwners.id
  from
  TaskImpl t, 
  OrganizationalEntityImpl potentialOwners        
  where
  exists (select 1
  from
  TaskImpl t2 
  left join t2.taskData.createdBy as createdBy
  left join t2.taskData.actualOwner as actualOwner 
  left join t2.subjects as subject
  left join t2.descriptions as description 
  left join t2.names as name
  where
  t2.id=t.id and
  t2.archived = 0 and          
  lower(t2.taskData.actualOwner.id) = lower(:userId) and
  t2.taskData.status in (:status) and


  (
  name.language = :language
  or t2.names.size = 0
  ) and


  (
  subject.language = :language
  or t2.subjects.size = 0
  ) and


  (
  description.language = :language
  or t2.descriptions.size = 0
  )) and

  potentialOwners in elements ( t.peopleAssignments.potentialOwners  )  and      
  t.archived = 0 and
  t.taskData.status in ('Created', 'Ready', 'Reserved', 'InProgress', 'Suspended') 

        </query>
          <!-- hint name="org.hibernate.timeout" value="200"/ -->
</named-query>

       

      And in the TaskQueryServiceImpl method i made some changes in the parameters of the named query invocation:

       

       List<Object[]> tasksPotentialOwners = (List<Object[]>) pm.queryWithParametersInTransaction("TasksOwnedPotentialOwnersByTaskIdsNO1000LIMIT",
                  pm.addParametersToMap("userId", userId, "status", status, "language", language));

       

       

      Is another solution that not requires this kind of changes to avoid that Oracle error?

      If is not, how this solution can be included in the next releases?

       

      Greetings!