Error when querying for cases by caseDefinitionId and caseInstanceId

It’s a bit of a dumb thing to do, but an exception is being raised if I execute the following code (Flowable 6.5). I know it doesn’t make any sense to do this query, I’m just testing my REST API on top of Flowable’s Java API.
It probably makes more sense to throw a FlowableException when trying to use both methods instead of letting it reach the database.

2020-02-06 17:02:10.176 [ERROR] [default task-1] CommandContext - Error while closing command context
### Error querying database.  Cause: Incorrect syntax near 'RES'.
### The error may exist in org/flowable/cmmn/db/mapping/entity/CaseInstance.xml
### The error may involve org.flowable.cmmn.engine.impl.persistence.entity.CaseInstanceEntityImpl.selectCaseInstancesByQueryCriteria-Inline
### The error occurred while setting parameters
### SQL: SELECT SUB.* FROM (          select distinct RES.*  , row_number() over (order by RES.ID_ asc) rnk FROM ( select distinct RES.*  , CASE_DEF.KEY_ as CaseDefinitionKey, CASE_DEF.ID_ as         CaseDefinitionId, CASE_DEF.NAME_ as CaseDefinitionName, CASE_DEF.VERSION_ as CaseDefinitionVersion, CASE_DEF.DEPLOYMENT_ID_ as DeploymentId         from ACT_CMMN_RU_CASE_INST RES         inner join ACT_CMMN_CASEDEF CASE_DEF on RES.CASE_DEF_ID_ = CASE_DEF.ID_                                         WHERE  RES.ID_ = ?                                                        RES.CASE_DEF_ID_ = ?                          )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ?
### Cause: Incorrect syntax near 'RES'.

True, the query doesn’t make much sense (as the caseInstanceId is already unique), but it should work nevertheless. Fixed here:

1 Like