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