Hibernate 6 OrderBy in the wrong orders

I created a repo to replicate the issue with Hibernate 6 GitHub - gaetannandelec-ibboost/hibernate6-orderby

I have the following classes :

@Table(name = "tasks")
public class Task {

	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

	@OneToMany(mappedBy = "task", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
	@OrderBy("id DESC")
	@Fetch(value = FetchMode.SUBSELECT)
	private List<TaskVersion> taskVersions;
@Table(name = "task_versions")
public class TaskVersion {

	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

	@ManyToOne(fetch = FetchType.EAGER)
	@JoinColumn(name = "assignee", nullable = true)
	private User assignee;

	@ManyToOne(fetch = FetchType.EAGER)
	@JoinColumn(name = "task_id", nullable = false)
	private Task task;

	@Column(name = "name")
	private String name;
@Table(name = "users")
public class User {

	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;

	@ManyToMany(fetch = FetchType.EAGER, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
	@JoinTable(name = "user_groups", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "group_id"))
	// @Fetch(FetchMode.SUBSELECT)
	private Set<Group> groups;

	@ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
	@JoinTable(name = "user_organisations", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "organisation_id"))
	// @Fetch(FetchMode.SUBSELECT)
	private Set<Organisation> organisations;

Upon retrieve a task by ID, it generates the following SQL with hibernate 6.1.5.Final :

 select t1_0.task_id,t1_0.id,a1_0.id,g1_0.user_id,g1_1.id,g1_1.name,o1_0.user_id,o1_1.id,o1_1.name,t1_0.name 
 from task_versions t1_0 
 left join users a1_0 on a1_0.id=t1_0.assignee 
 left join (user_groups g1_0 join groups g1_1 on g1_1.id=g1_0.group_id) on a1_0.id=g1_0.user_id 
 left join (user_organisations o1_0 join organisations o1_1 on o1_1.id=o1_0.organisation_id) on a1_0.id=o1_0.user_id
 where t1_0.task_id=? order by g1_1.name,o1_1.name ,t1_0.id desc;

I would expect the order by to be build like this :
order by t1_0.id desc , g1_1.name,o1_1.name;

Worth noting that with hibernate 5.6.14.Final, it generates the following query :

select taskversio0_.task_id as task_id3_2_0_, taskversio0_.id as id1_2_0_, taskversio0_.id as id1_2_1_, taskversio0_.assignee as assignee2_2_1_, taskversio0_.task_id as task_id3_2_1_, user1_.id as id1_6_2_ 
from task_versions taskversio0_ 
left outer join users user1_ on taskversio0_.assignee=user1_.id 
where taskversio0_.task_id=? order by taskversio0_.id desc

So i am not entirely sure if the issue lies with the OrderBy or fetching the ManyToMany relationship

This looks like a bug. Please create a JIRA issue for this.

Thank you. [HHH-15885] - Hibernate JIRA

1 Like


After migration to Springboot3 and Hibernate 6, we are facing the same issue. Is there any news when the fix will be available?



We have the same issue, any update on this bug ?