The SQL generated by the Historical Process Instance Query API incorrectly assumes that all ACT_HI_PROCINST.PROC_DEF_ID_ values will contain the process definition key e.g.

The following code:

execution.getProcessEngineServices()
.getHistoryService()
.createHistoricProcessInstanceQuery()
.processDefinitionKey("myFairlyLongButNotExcessivelyLongProcessKey")
.processInstanceBusinessKey("myBusinessKey").count()

...will generate the following SQL:

select count(distinct RES.ID_) from ACT_HI_PROCINST RES inner join ACT_RE_PROCDEF DEF on RES.PROC_DEF_ID_
= DEF.ID_ inner join ACT_HI_VARINST A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_ WHERE RES.PROC_DEF_ID_
like 'myFairlyLongButNotExcessivelyLongProcessKey:%:%' and RES.BUSINESS_KEY_ = 'myBusinessKey'

The flaw in this logic is that the BpmnDeployer.getProcessDefinitionId method will return a process definition identifier that does not contain the process key if the combined length of the process key + ':' + deployed version number + ':' + id exceeds 64 characters. This is pretty easy to achieve when using the StrongUuidGenerator. In this case "WHERE RES.PROC_DEF_ID_
like 'myFairlyLongButNotExcessivelyLongProcessKey:%:%'" will not match and records in the database and the query will return 0 rows.

From a SQL perspective, it would be safer to query the ACT_RE_PROCDEF.KEY_ column rather than make assumptions about the format of the process definition id.

Test case attached.