NullHandlingOrder not working

Hi,

I’m currently using Flowable 6.5.0 with MS SQL Server and when I try to apply a NullHandlingOrder on the TaskQuery API, I’m getting the following error:

Any idea why this might be happening?

Bellow is the code being executed:

List<Task> tasks = getProcessEngine().getTaskService().createTaskQuery()
                .orderBy(TaskQueryProperty.DUE_DATE, Query.NullHandlingOnOrder.NULLS_LAST)
                .asc()
                .list();

For clarification:
I’ve overrided the engines default Task Manager because I need the feature provided by this pull request. But it’s a copy of the files from that commit so that shouldn’t be the issue.

Thank you,
David.

Have you tried:

# spring.jpa.properties.hibernate.order_by.default_null_ordering=first
spring.jpa.properties.hibernate.order_by.default_null_ordering=last

Ref:

The code that seems to be adding the _order_null is here: https://github.com/flowable/flowable-engine/blob/master/modules/flowable-engine-common/src/main/java/org/flowable/common/engine/impl/db/ListQueryParameterObject.java#L91

Could you debug and see what happens there when executing the query?

I don’t want to change the default behavior, I just need to apply this query in specific cases

It seems like it’s executing the code that it is supposed to do.

I’m not in any kind familiar with the row_number() and over() functions so that’s why I can’t figure out what’s actually wrong here.

Is there anything in particular you want me to look at ?

In the screen you posted in your first post, the DUE_DATE__order_null looks off. So I was wondering why the sql is what it is, given the code, as it doesn’t seem to do what it should do (i.e. what values do you see for the lines you highlighted in red)

I don’t think I understand what you mean with “DUE_DATE__order_null looks off”.
Anyway, I can show the value of the variables in debug mode:

I tried to reproduce the problem by making a unit test in your repository but I haven’t found a way to execute them against a MSSQL database :frowning:

Are you running from IntelliJ?

If yes then you will need to set the following parameters as VM Options:

-Djdbc.url="<url>"
-Djdbc.username=<user>
-Djdbc.password=<passs>
-Djdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

You can also change the default datasource properties in flowable.cfg.xml.

Cheers,
Filip

Alright, so it took me some time but I found a way to reproduce the bug in unit test. Looks like this only happens when using TaskQuery#listPage() method.

    @Test
    @Deployment(resources = { "org/flowable/engine/test/api/task/TaskQueryTest.testProcessDefinition.bpmn20.xml" })
    public void testNullHandlingOrder() {
        ProcessInstance firstProcessInstance = runtimeService.startProcessInstanceByKey("oneTaskProcess");
        ProcessInstance secondProcessInstance = runtimeService.startProcessInstanceByKey("oneTaskProcess");

        Task firstTask = taskService.createTaskQuery().processInstanceId(firstProcessInstance.getId()).singleResult();
        Task secondTask = taskService.createTaskQuery().processInstanceId(secondProcessInstance.getId()).singleResult();

        taskService.setDueDate(secondTask.getId(), new Date());

        List<Task> tasks = taskService.createTaskQuery()
                .processDefinitionKey("oneTaskProcess")
                .orderBy(TaskQueryProperty.DUE_DATE, Query.NullHandlingOnOrder.NULLS_LAST)
                .asc()
                .listPage(0, 10);

        assertEquals(secondTask.getId(), tasks.get(0).getId());
        assertEquals(firstTask.getId(), tasks.get(1).getId());
    }