`nulls first` clause is added to `order by` against a MySQL database after SpringBoot update from 3.4.13 to 3.5.11

Hibernate ORM
1

Hi Hibernate Community!

Since I updated SpringBoot from 3.4.13 (–> hibernate-core 6.6.39.Final) to 3.5.11 (–> hibernate-core 6.6.42.Final), a nulls first clause is added to order by which causes an error against a MySQL database.
How come the nulls first clause is added even though a MySQL database is present?
Shouldn’t that be omitted in that case?
Is there maybe a problem determining the database vendor?

I already tried setting jpa.properties.hibernate.column_ordering_strategy to legacy, but to no avail.
I also tried setting jpa.properties.hibernate.order_by.default_null_ordering to none, but still no avail.
I also tried setting jakarta.persistence.database-product-name to MySQL, but still no avail.

I also tried explicitly setting the hibernate-core version, first to 6.6.39.Final and then to 6.6.44.Final, but none of that fixed it.

As using different hibernate-core versions had no effect, could it be the case that this behavior is caused by Spring JPA?

I can’t imagine that I am the only one facing this problem (nulls first clause is added to order by against a MySQL database after SpringBoot update from 3.4.13 to 3.5.11).
Could someone please help me out on this one?
Maybe I’m asking in the wrong place… If so, could someone please point me to where I should ask?

Best Regards,
Andy

2

We have plenty of tests for this, so the only reason why you could be seeing this is if you override the Dialect somehow. I don’t know what your application does or Spring does, but maybe something overrides that?

3

Maybe dialect detection is broken.
If I set the dialect at runtime, could that fix it?

4

I just found the root cause:
I had this in my code:

Sort.Order.asc(fieldSortBy).nullsFirst();

:see_no_evil_monkey:
Looks like this was present for quite some time, but got active through the version update.
→ Solved! :smiley:
Thanks for your support!

5

I don’t know what that is, maybe a Spring feature? Either way, we test the emulation for MySQL, so we know for sure that this works. It must be something on your end or a Spring problem.

6

Yes, it’s something on my end concerning a Spring feature.
org.springframework.data.domain.Sort.nullsFirst()
Apparently this caused no problem with older versions, but gets honored now.
Removing the .nullsFirst(), which should never have been there, from my code solved the Problem. :smiley:

7

You can write in HQL order by x nulls first and Hibernate ORM will correctly emulate this on MySQL. My guess is that you’re not using Hibernate ORM in this particular case. Is this Spring Data JDBC maybe?

8

Could be.
I have set spring.jpa.properties.hibernate.show_sql=true on my test system and it prints out the queries starting with Hibernate: , so I was assuming that Hibernate ORM is involved.

Anyway, the problem is solved, so I’m happy :grin: