NullHandlingOrder not working

Flowable Engine
1

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:

image
image1797×340 31 KB

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.

2

Have you tried:

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

Ref:

3

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?

4

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

5

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

image
image1836×885 111 KB

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 ?

6

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)

7

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:

image
image1872×947 135 KB

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:

8

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

9

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());
    }
10

Thanks a lot for the test case @DavidPLamas. It helped a lot to locate the fix.

The fix is done as part of https://github.com/flowable/flowable-engine/commit/b0d2adcab837d6d8cabdcdd1335c23f19344fef1 and will be part of the next release.

Cheers,
Filip