Native query with distinct keyword returns wrong result

Since there are no TaskDefinition registry (like for process definitions), I figured I could query for distinct tasks names using a native query like this:

historyService.createNativeHistoricTaskInstanceQuery().sql(“SELECT DISTINCT(T1.NAME_) FROM " + managementService.getTableName(HistoricTaskInstance.class) + " T1”).list();

The result contains 14 rows all with the same task name. If I execute it directly agains the db it will result in 14 distinct names.

What am I missing? Does anyone have a better idea on how I can get all task definition names without querying all historic tasks?


Hi Erik,
That’s an interesting question.
Im not exactly sure what you are asking, but I think you want a list of all tasks in the system.
If that’s the question, we don’t actually break processe/case definitions apart, they are retained in BPMN/CMMN format. Therefore, in order to get a list of all deployed task names you would need to parse each of the models. Keep in mind, multiple tasks in different processes/cases may have the same name, but are actually different tasks.

The ACT_HI_TASKINST table actually maintains a list of tasks that have been executed.
Looking at the code you have, the query that results should be:
And, the native queries are passed directly so you should be getting the same result whether you are making the call and returning the list() or if you are calling the database directly.


The reason I am asking is because I am constructing a prometheus metrics collector. I am collecting metrics of running/finished processes per definition, deadletter and timer jobs for example. I wanted a metrics for active usertasks by name also, and since we have too much history in order for me to query finished tasks I came up with the native query idea. Really strange thing that it does not work. However as you mentioned I will not get tasks never run this way anyway, so I will try to query the models instead.


Solved it like this instead:

        BpmnModel bpmnModel = repositoryService.getBpmnModel(definition.getId());
        List<UserTask> userTasks = bpmnModel.getMainProcess().findFlowElementsOfType(UserTask.class, true);

That’s great, would it be possible for you to mark this as the solution for future reference by others?

Well, it did not solve the problem with the native query returning the same task name for all 13 unique task names I had in my table, so marking it as resolved seems not the right thing to do here :frowning:

Hey @billerby,

Which version of Flowable did you try your query one?



This was on 6.7.2.


I had a look at this @billerby and the reason why it doesn’t work is most likely due to the way MyBatis does its mappings.

In our HistoricTaskInstance.xml result map we have an id mapping for ID_. MyBatis treats this in a special way. I guess this is the reason why you see the same entry in the list. If you debug you’ll see that the same HistoricTaskInstanceEntityImpl is in the list.

What would work is to do something like:

    .sql(“SELECT DISTINCT(T1.NAME_) as ID_ FROM " + managementService.getTableName(HistoricTaskInstance.class) + " T1”)

This way you will have your unique name in the HistoricTaskInstance#getId and MyBatis will be happy since it will correctly get the data it needs.

Note: This would only work if you want to get a unique entry for a single column, if you want to do it for two, then there isn’t a workaround.

In any case, what I would suggest in case you need to run some custom more complex queries, would be to create your own MyBatis Mappers or just use something like Spring JdbcTemplate and run the queries you want to run.