Search code examples
hibernatejpaspring-data-jpah2

Hibernate 6 H2 dialect override: Nulls precedence ignored

Recently upgraded my project to Spring boot 3 and also Hibernate 6.1.5 (from 5.x). I'm struggling migrating the different dialects we used to extend.

The way to override Dialect has changed so I migrated my H2 dialect extension from this (hibernate 5):

public class ExtendedH2Dialect extends H2Dialect {
    @Override
    public String renderOrderByElement(String expression, String collation, String order, NullPrecedence nulls) {
        return super.renderOrderByElement(expression, collation, order, NullPrecedence.LAST);
    }
}

To this (hibernate 6):

public class ExtendedH2Dialect extends H2Dialect {
    public ExtendedH2Dialect(DialectResolutionInfo info) {
        super(info);
    }

    @Override
    public NullOrdering getNullOrdering() {
        return NullOrdering.LAST;
    }
}

But the null ordering seems to be completely ignored by JPA queries. As an example if I was doing this in hibernate 5:

entityManager.createQuery("from CityEntity order by name").getResultList();

The generated query was:

select cityentity0_.id as id1_0_, cityentity0_.name as name2_0_ from city_entity cityentity0_ order by cityentity0_.name nulls last

And now with hibernate 6 it's:

select c1_0.id,c1_0.name from city_entity c1_0 order by c1_0.name

My custom dialect is still registered "HHH000400: Using dialect: com.example.demoextendh2.ExtendedH2Dialect".

I uploaded a minimal reproducible project here

Did I do something wrong ? Can't find a lot of documentation about custom dialect migration.

Solution

  • Looks like the the default ordering property needs to be set in the project's configuration (application.yml, or what have you):

    hibernate.order_by.default_null_ordering: 'last'

    And in your dialect only needs to return true on supportsNullPrecedence() calls but this should already be the case. (Don't override the getNullOrdering() method).

    This fixed our problem. It seems that when decide if it needs to add ordering for nulls, AbstractSqlAstTranslator compares the default sort required in the configuration and the sort provided by the Dialect. It will only add an explicit null sort if they differ and the dialect support null sort. Meaning, if the Dialect already sorts with nulls last and you require nulls last, hibernate knows it doesn't need to add explicit ordering since it should get it automatically (according to the dialect)