Hi, I’m to create query with ordering by field of related entity, like this:
“SELECT p FROM Patient p ORDER BY p.doctor.name”
And sql-query that Hibernate builds based on that of JPQL uses CROSS JOIN with condition (effectively, inner join):
select patient0_.id as id1_1_, patient0_.doctor_id as doctor_i3_1_, patient0_.name as name2_1_
from patient patient0_ cross join doctor doctor1_
where patient0_.doctor_id=doctor1_.id order by doctor1_.name
As a side-effect, all patients with nulls in ‘doctor’ field are excluded from result set. Is there any option I can switch to hint Hibernate to use LEFT JOIN in such cases? Can’t such a behavior be considered a bug? From common sense’ point, just adding ordering shoud not affect result count.
Example is intentionally simplified. In real world it’s a dynamically built criteria-query with variable set of filters and sortings. So I can not work it around and use explicit LEFT JOIN in JPQL.
I reproduced behavior of simplified example in version 5.3.9.Final and in latest 5.4.15.Final.
Appreciate any help.