1 Reply Latest reply on Oct 29, 2014 4:25 AM by Maciej Swiderski

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

    Fer Gia Newbie

      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!