Customer noticed that if they use "order-by-clause":"CREATEDON desc" in their custom queries, the order is not correct. When they use "order-by-clause":"createdon desc" the order is correct.
 
I could see the same behaviour in my environment. Also, this seems to be the problem in the latest version of RHPAM. It worked fine in previous versions. The database is Postgres.
 
For instance, the result of the following custom query:
 
{
  "query-name" : "jbpmHumanTasksPO",
  "query-source" : "${org.kie.server.persistence.ds}",
  "query-expression" : "select t.id as TASKID, t.name as NAME,  t.FORMNAME AS FORMNAME, t.description as DESCRIPTION, t.subject as SUBJECT, t.actualowner_id as actualowner, po.entity_id as POTOWNER, p.processinstancedescription as PROCESSINSTANCEDESCRIPTION, t.CREATEDON as CREATEDON, t.CREATEDBY_ID as CREATEDBY, t.EXPIRATIONTIME as EXPIRATIONDATE, t.priority as PRIORITY, t.STATUS as STATUS, t.PROCESSINSTANCEID as PROCESSINSTANCEID, t.deploymentid as DEPLOYMENTID, t.PROCESSID as PROCESSID from TASK t left join PEOPLEASSIGNMENTS_POTOWNERS po on t.id=po.task_id inner join PROCESSINSTANCELOG p on t.processinstanceid = p.processinstanceid",
  "query-target" : "USER_GROUPS_TASK"
}
 
will give the following in my environment:
 
 

 
 
and I'll get the following with "order-by-clause":"createdon desc":
 
[bkramer@bkramer bin]$ curl -X POST -u 'rhpamAdmin:rhpamAdmin123!' "http://localhost:8080/kie-server/services/rest/server/queries/definitions/jbpmHumanTasksPO/filtered-data?mapper=UserTasksWithPotOwners&page=0&pageSize=10" -H "accept: application/json" -H "content-type: application/json" -d '{"query-params":[

{"cond-column":"priority","cond-operator":"EQUALS_TO","cond-values":["0"]}

,{"cond-column":"potowner","cond-operator":"EQUALS_TO","cond-values":["PM"]},{"cond-column":"status","cond-operator":"IN","cond-values":["Ready","Reserved","InProgress"]}],"order-by-clause":"createdon desc"}' | grep task-id
    "task-id" : 34,
    "task-id" : 33,
    "task-id" : 32,
    "task-id" : 31,
    "task-id" : 30,
    "task-id" : 29,
    "task-id" : 3,
 
If I use "order-by-clause":"CREATEDON desc":
 
[bkramer@bkramer bin]$ curl -X POST -u 'rhpamAdmin:rhpamAdmin123!' "http://localhost:8080/kie-server/services/rest/server/queries/definitions/jbpmHumanTasksPO/filtered-data?mapper=UserTasksWithPotOwners&page=0&pageSize=10" -H "accept: application/json" -H "content-type: application/json" -d '{"query-params":[

{"cond-column":"priority","cond-operator":"EQUALS_TO","cond-values":["0"]}

,{"cond-column":"potowner","cond-operator":"EQUALS_TO","cond-values":["PM"]},{"cond-column":"status","cond-operator":"IN","cond-values":["Ready","Reserved","InProgress"]}],"order-by-clause":"CREATEDON DESC"}' | grep task-id

  "task-id" : 30,
  "task-id" : 34,
  "task-id" : 33,
  "task-id" : 29,
  "task-id" : 3,
  "task-id" : 32,
  "task-id" : 31,
 
the order is different and not correct.
 
I also used "order-by" instead of "order-by-clause" with "createdon desc" and "CREATEDON desc" but with this I got the same result in both case but incorrect:
 
[bkramer@bkramer bin]$ curl -X POST -u 'rhpamAdmin:rhpamAdmin123!' "http://localhost:8080/kie-server/services/rest/server/queries/definitions/jbpmHumanTasksPO/filtered-data?mapper=UserTasksWithPotOwners&page=0&pageSize=10" -H "accept: application/json" -H "content-type: application/json" -d '{"query-params":[

{"cond-column":"priority","cond-operator":"EQUALS_TO","cond-values":["0"]}

,{"cond-column":"potowner","cond-operator":"EQUALS_TO","cond-values":["PM"]},{"cond-column":"status","cond-operator":"IN","cond-values":["Ready","Reserved","InProgress"]}],"order-by":"CREATEDON desc"}' | grep task-id
   "task-id" : 30,
    "task-id" : 34,
    "task-id" : 33,
    "task-id" : 29,
    "task-id" : 3,
    "task-id" : 32,
    "task-id" : 31,I would like t
[bkramer@bkramer bin]$ curl -X POST -u 'rhpamAdmin:rhpamAdmin123!' "http://localhost:8080/kie-server/services/rest/server/queries/definitions/jbpmHumanTasksPO/filtered-data?mapper=UserTasksWithPotOwners&page=0&pageSize=10" -H "accept: application/json" -H "content-type: application/json" -d '{"query-params":[

{"cond-column":"priority","cond-operator":"EQUALS_TO","cond-values":["0"]}

,{"cond-column":"potowner","cond-operator":"EQUALS_TO","cond-values":["PM"]},{"cond-column":"status","cond-operator":"IN","cond-values":["Ready","Reserved","InProgress"]}],"order-by":"createdon desc"}' | grep task-id
    "task-id" : 30,
    "task-id" : 34,
    "task-id" : 33,
    "task-id" : 29,
    "task-id" : 3,
    "task-id" : 32,
    "task-id" : 31,
 
Customer is taking these parameters from the public class [QueryResultMapper](https://github.com/kiegroup/jbpm/blob/main/jbpm-services/jbpm-services-api/src/main/java/org/jbpm/services/api/query/QueryResultMapper.java#L90) and in this class the column is in upper case.