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 :

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

	@Id
	@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;
}
@Entity
@Table(name = "task_versions")
public class TaskVersion {

	@Id
	@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;
}
@Entity
@Table(name = "users")
public class User {

	@Id
	@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"))
	@OrderBy("name")
	// @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"))
	@OrderBy("name")
	// @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