AnsweredAssumed Answered

ORA-00918 column ambiguously defined when querying historic task instances

Question asked by fmeschia on Sep 4, 2013
Latest reply on Sep 7, 2013 by fmeschia
Hello,

I am using Activiti 5.13 with Oracle DB 11g. When I use the REST API to query for a historical task instance given a set of process variable, like this:

POST localhost:9090/activiti-rest/service/query/historic-task-instances
{
  "includeProcessVariables":true,
    "processVariables": [
      {
        "name":"requestId",
        "value":44,
        "operation":"equals",
        "type":"long"
      }
    ]
}

I get Error 500 and in the web container logs I see this:

WARNING: Exception or error caught in resource
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

### The error may exist in org/activiti/db/mapping/entity/HistoricTaskInstance.xml
### The error may involve org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntity.selectHistoricTaskInstancesWithVariablesByQueryCriteria-Inline
### The error occurred while setting parameters
### SQL: select * from ( select a.*, ROWNUM rnum from (     select distinct RES.*,     VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_,     VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, VAR.TASK_ID_ as VAR_TASK_ID_,     VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_,      VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LONG_ as VAR_LONG_                  from ACT_HI_TASKINST RES                                     left outer join ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null                                                              inner join ACT_HI_VARINST A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_                              WHERE  A0.TASK_ID_ is null                                        and A0.NAME_= ?                             and A0.VAR_TYPE_ = ?                                                        and A0.LONG_                =              ?                order by ID_ asc        ) a where ROWNUM < ?) where rnum  >= ?
### Cause: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

   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:284)
   at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:275)
   at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:265)
   at org.activiti.engine.impl.persistence.entity.HistoricTaskInstanceEntityManager.findHistoricTaskInstancesAndVariablesByQueryCriteria(HistoricTaskInstanceEntityManager.java:63)
   at org.activiti.engine.impl.HistoricTaskInstanceQueryImpl.executeList(HistoricTaskInstanceQueryImpl.java:89)
   at org.activiti.engine.impl.AbstractQuery.execute(AbstractQuery.java:143)
   at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:61)
   at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
   at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
   at org.activiti.engine.impl.AbstractQuery.listPage(AbstractQuery.java:124)
   at org.activiti.rest.api.AbstractPaginateList.paginateList(AbstractPaginateList.java:75)
   at org.activiti.rest.api.history.HistoricTaskInstanceBaseResource.getQueryResponse(HistoricTaskInstanceBaseResource.java:167)
   at org.activiti.rest.api.history.HistoricTaskInstanceQueryResource.queryProcessInstances(HistoricTaskInstanceQueryResource.java:28)

The only column name that may be ambiguous in this case is ROWNUM, which I guess is used to page results. No error shows up if I don't use process variables as a query parameter.

Can you help?

Francesco

Outcomes