Hello, we are migrating our application to Spring Boot 3.2 and Hibernate 6.6. One of our applications uses a DB2 z/Os database. We are creating our queries with the Criteria API and we are having a problem with the generated order by clauses. The result will be sorted according the ordinal positions of the resultset. So the generated query contains something like order by 1,3, if the result will be sorted on the first column and after that on the third.
The problem is that db2 needs a blank after the comma. So it should be order by 1, 3. Queries without the blank will only be sorted according the first column, the rest gets ignored. Is this a already known problem or a new bug?
In our code the order by clauses will be added in a generic method like that:
LinkedHashMap<SingularAttribute<T, ?>, Boolean> orderBy
...
if (orderBy != null && !orderBy.isEmpty()) {
List<Order> orders = new ArrayList<>(orderBy.size());
for (Entry<SingularAttribute<T, ?>, Boolean> order : orderBy.entrySet()) {
if (order.getValue() != null && order.getValue().booleanValue()) {
orders.add(builder.desc(root.get(order.getKey())));
}
else {
orders.add(builder.asc(root.get(order.getKey())));
}
}
select.orderBy(orders);
}
...
return query.getResultList();
The used dialect is the DB2zDialect.
I guess the problem comes from the DECIMAL=COMMA setting in IBM DB2 for z/OS. So “1,3” will be interpreted as one parameter in environments with such setting. But in my opinion the generated query should work for all environments regarding the decimal settings. Therefore the blank should be added.