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?

Thanks!
/Erik

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:
SELECT DISTINCT(T1.NAME_) FROM ACT_HI_TASKINST T1
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.

Hi!

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.

Thanks!

Solved it like this instead:

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

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

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: