ORA-01795: maximum number of expressions in a list is 1000 (proposed fix)
fgiannetti Oct 23, 2014 10:23 AMHi 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!