I am in the process of migrating an inherited application from springboot 2.7 and hibernate 5 to springboot 3 and hibernate 6 but I am encountering some problems with hibernate. Currently I am getting the following error when running query.getResultList():
... [A temporary table could not be created because there is no available system temporary table space that has a compatible page size.. SQLCODE=-1585, SQLSTATE=54048, DRIVER=4.19.77]
I narrowed down the issue to statement that it's being used for sorting:
final SortPropertiesResolver sortResolver = new SortPropertiesResolver.Builder()
.addMapping("displayTitle", "LOWER(COALESCE(dna.value, p.name))")
.build();
The final query looks something like this:
FROM Profile$Active p LEFT JOIN FETCH p.displayNameAttribute dna WHERE p.client.id = :clientId order by LOWER(COALESCE(dna.value, p.name)) asc
If I remove the coalesce function it runs through but with it, it simply fails with error already. I was checking the documentation and the migration guide and the only thing that is mentioned about temporary tables is the strategy for bulk operations and I couldn't get it to work using any of the strategies. Some wouldn't even allow the application to run because their constructor was not compatible with my configuration which only passes the dialect to the constructor.
Worth mentioning that this query is aimed at a db2 instance.
I am not an hibernate expert or close to it and I hope that I am missing something obvious to some people here. If someone could point me out in the right direction I would be very thankful.