Using QueryProperty with HistoricProcessInstanceQuery


I am trying to lookup the historic processes, but I would like to use a queryProperty with it.
I implemented the QueryProperty in a somewhat similar fashion:

public class HistoricalProcessQueryProperty implements QueryProperty {

    private static final Map<String, String> PROPERTIES;
    private static final String START_TIME_PROPERTY_NAME = "RES.START_TIME_";

    static {
        Map<String, String> props = new HashMap<>();
        props.put("name", "lower(RES.NAME_)");
        props.put("definitionName", "lower(DEF.NAME_)");
        props.put("createdOn", START_TIME_PROPERTY_NAME);
        props.put("endedOn", "RES.END_TIME_");

        PROPERTIES = Collections.unmodifiableMap(props);

    private String name;

    public HistoricalProcessQueryProperty(final String name) { = PROPERTIES.getOrDefault(name, START_TIME_PROPERTY_NAME);

    public String getName() {

Based on the parameter of the input name, the instance will return with different properties.

The code where I use this class:

String property = order.getProperty();
processQuery.orderBy(new HistoricalProcessQueryProperty(property));

if (order.isAscending()) {
} else {

Where processQuery is a HistoricalProcessInstanceQuery

The very same implementation works fine when I use it with the current processes, that’s because different SQL query runs than what I pasted in here below. But it doesn’t order the result when I use this code with the historical processes.

I enabled the debug logging in flowable and if I am reading it correctly, then this is the query that runs whenever I call the API:

select * from ( select a.*, ROWNUM rnum from ( select distinct RES.*, DEF.KEY_ as PROC_DEF_KEY_, DEF.NAME_ as PROC_DEF_NAME_, DEF.VERSION_ as PROC_DEF_VERSION_, DEF.DEPLOYMENT_ID_ as DEPLOYMENT_ID_,
                                             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.LONG_ as VAR_LONG_ from ACT_HI_PROCINST RES left outer join ACT_RE_PROCDEF DEF on RES.PROC_DEF_ID_ = DEF.ID_ left outer join ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null
                                            WHERE RES.END_TIME_ is not NULL order by VAR.LAST_UPDATED_TIME_ asc, lower(DEF.NAME_) asc, VAR.LAST_UPDATED_TIME_ desc ) a where ROWNUM < 20001) where rnum >= 1

The relevant section is at the end:

order by VAR.LAST_UPDATED_TIME_ asc, lower(DEF.NAME_) asc, VAR.LAST_UPDATED_TIME_ desc

My query: “lower(DEF.NAME_) asc” which fits into the two VAR.LAST_UPDATED_TIME orders.
Now since the first LAST_UPDATED_TIME is there, my order doesn’t do much in my case, it won’t order the resultset.

If I take it out, then it works fine.

Flowable version: 6.3.1

Can somebody tell me what am I doing wrong?


Hi Tozo.

I tried to reproduce your issue in the simple jUnit test.

assertEquals(1, historyService.createHistoricProcessInstanceQuery().includeProcessVariables().orderBy(
        (QueryProperty) () -> "DEF.NAME_"

generated sql query

 ID_ and VAR.TASK_ID_ is null order by DEF.NAME_ asc, VAR.LAST_UPDATED_TIME_ asc

Is the problem related to a specific DB vendor?


Hi Martin,

Thanks for coming back on this.
Sorry, I forgot to mention, we are using Oracle:
Oracle Database 12c Enterprise Edition Release - 64bit Production
Oracle dependency:

Also: spring boot version: 2.0.4

I didn’t test it with any other databases, so I am not sure if it happens with other ones.


There should be no difference between h2 and oracle in these kind of queries. You can debug myBatis a find out how select is created or try to reproduce the issue in the simple jUnit test with h2.


Something that I just remembered. @tozo are you sure that you are not calling processQuery.orderBy(...) multiple times? As it what you are seeing can be caused if you set the order by multiple times.


To give you some context where I am using the query:

HistoricProcessInstanceQuery processQuery = historyService.createHistoricProcessInstanceQuery().finished();

if (!isSuperAdmin) {

processQuery = processQuery.includeProcessVariables();

ServiceUtils.processFilterCriteria(filterCriteriaList, processQuery);

long size = processQuery.count();
LOG.debug("The returned size of the list: {}", size);

// Get the first order (there shouldn't be more anyway)
if (pageable.getSort().iterator().hasNext()) {
    Sort.Order order = pageable.getSort().iterator().next();
    String property = order.getProperty();
    processQuery.orderBy(new HistoricalProcessQueryProperty(property));

    if (order.isAscending()) {
    } else {

List<HistoricProcessInstance> historicProcessInstances;

if (all) {"Looking up all the historical process instances");
    historicProcessInstances = processQuery.list();
} else {"Looking up only a subset of historical process instances");
    historicProcessInstances = processQuery.listPage(pageable.getPageNumber() * pageable.getPageSize(), pageable.getPageSize());

The only thing I do in the “ServiceUtils.processFilterCriteria” is to filter for specific processes. I do it by this:

for (FilterCriteria criteria : filterCriteriaList) {
    switch (criteria.getKey()) {
        case PROCESS_NAME:
            if (query instanceof ProcessInstanceQuery) {
                ((ProcessInstanceQuery) query).processInstanceNameLikeIgnoreCase("%" + criteria.getValue() + "%");
            } else if (query instanceof HistoricProcessInstanceQuery) {
                ((HistoricProcessInstanceQuery) query).processInstanceNameLikeIgnoreCase("%" + criteria.getValue() + "%");

So I don’t call the orderBy multiple times and if none of these functions should cause this issue, then I will write some tests and try to figure out where is the problem.


I think I know where the problem is.
Probably I shouldn’t have reused the processQuery.

As you can see in my previous post I make two calls using the same query:



processQuery.list(); or processQuery.listPage();

I noticed that when the count function called, it triggers the executeCount -> checkQueryOk functions in HistoricProcessInstanceQueryImpl and since I want the processVariables to be included, it eventually calls addOrder function from ListQueryParameterObject class which updates the orderByColumns with VAR.LAST_UPDATED_TIME_ asc.

Once it returns with the result, I set the orderBy in my code, which will just append my order to the existing orderByColumns property that still has the previously set order. When eventually the list or listPage gets called, it appends the final VAR.LAST_UPDATED_TIME_ asc and that’s why I have that repeated.

At this point I suppose I have two options:

  • Do not reuse the processQuery
  • Somehow reset the orderByColumns variable

I noticed that there is a setOrderByColumns function on ListQueryParameterObject, which is extended indirectly by the HistoricProcessInstanceQueryImpl. Unfortunately the historyService.createHistoricProcessInstanceQuery() returns with the HistoricProcessInstanceQuery which doesn’t expose this function.

I suppose that was a design decision.
I could cast the HistoricProcessInstanceQuery to a HistoricProcessInstanceQueryImpl and reset the orderByColumns, but I don’t know if something else is still messed up, therefore I will use a new processQuery for the count(), it’s safer.


Really good analysis @tozo

The queries are stateful by design, perhaps we need to do something so that calling the execution methods (count, list, listPage) does not affect the underlying query and there is no surprise for the user.

For the time being it is safer to just create a new query every time.


Something else that I forgot to mention, when you are doing the count you should avoid adding includeProcessVariables. Which means that you can reuse the query, you will first do a count, then add includeProcessVariables and your orderBy, which would then work properly

Yes, I could move the includeProcessVariables after the count(), but I would be hesitant doing that, since if some other developer comes onto the project and they move things around, they might hit the same problem that I did (since they are not aware of this detail).

I just created a function that initiates a new processQuery with all the necessary flags and that function gets reused multiple times.

in 6.4.1 when I run list(), it leads to the following:

protected void checkQueryOk() {

   ** if (includeProcessVariables) {

Which runs orderBy() over what I have previously meaned to order by, and makes the query sorting options useless.
I need to include process variables, however I need custom sorting as well.
Am I doing anything wrong, or is that a problem in current backlog?

Yesterday, I also faced same problem that sorting not work well HistoricProcessInstanceQuery.(in 6.6.0)

Looks every time order by VAR.LAST_UPDATED_TIME_ asc will be set unintentionally as first order.
I’m not sure the reason of this behavior.

Anyway to avoid problem, I have implemented HistoricProcessInstanceQueryInterceptorImpl and set it in ProcessConfiguration, then I could overwrite it.

it might be some workaround.

about my example code(quick fix)

if you set

or other order then, this will delete order by "lastUpdateDate asc "

public class HistoricProcessInstanceQueryInterceptorImpl implements HistoricProcessInstanceQueryInterceptor {
    public void beforeHistoricProcessInstanceQueryExecute(HistoricProcessInstanceQuery processInstanceQuery) {
            HistoricProcessInstanceQueryImpl impl = (HistoricProcessInstanceQueryImpl) processInstanceQuery;
            if(impl.getOrderByColumns() == null) return;
            List<String> orderColumns = Arrays.asList(impl.getOrderByColumns().split(","));
            if(orderColumns.size()>1) {
                if (orderColumns.contains("VAR.LAST_UPDATED_TIME_ desc ")) {
                    impl.getOrderByColumnMap().put("VAR.LAST_UPDATED_TIME_", Boolean.FALSE);
                } else {
                String myOrder = impl.getOrderByColumns().replace(", VAR.LAST_UPDATED_TIME_ asc","");
                processInstanceQuery = impl;
        }catch (Exception e){


    public void afterHistoricProcessInstanceQueryExecute(HistoricProcessInstanceQuery processInstanceQuery, List<HistoricProcessInstance> processInstances) {