Invert Order By Nulls behaviour

Hibernate ORM
1

Hi,
Iam migrating a big project from MSSQL to Postgres and I have issues with the ordering of null values. The order by nulls behaviour is inverse between these two RDBMS. In postgres the sorting of nulls is:

By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.

And in MSSQL:

ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. NULL values are treated as the lowest possible values.

I have a lot of integration tests that test exactly this behaviour and I would like to keep them as their are. In Hibernate I can set the hibernate.order_by.default_null_ordering to either first or last but this is not the same as my desired behaviour.

This Stackoverflow suggests implemeting some weird interceptor to solve this issue. Furthermore there is the Idea to extend the PostgreSQL10Dialect (which apparently dose not exist anlymore) to have this functionallity baked into hibernate directly. But I can’t find the method renderOrderByElement anywere in Hibernate 6.

What whould you suggest to do?
What is the best solution for this problem?
Shouldn’t there be a native option in hibernate to allow the seamless use of thoses RDBMS?

2

I would suggest you remove your assumptions and specify the implicit default null ordering in all your queries.

So far, you can only configure whether you want nulls first or last by default, although Hibernate ORM could also support configuring that nulls are “greatest”/“smallest” and go from there, you will have to implement that yourself in Hibernate ORM if you want that feature.

Look for uses of org.hibernate.boot.spi.SessionFactoryOptions#getDefaultNullPrecedence and you will find the code that handles this right now. There is an enum NullOrdering which fits what you want.