We're updating the issue view to help you get more done. 

sorting accross entity associations filter null values because of unexpected cross joins

Description

Given following entities:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 @Entity public class User { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @OneToOne(fetch = FetchType.LAZY) @JoinColumn private Chair chair; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public Chair getChair() { return chair; } public void setChair(Chair chair) { this.chair = chair; } }
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 @Entity public class Chair { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @OneToOne( fetch = FetchType.LAZY, mappedBy = "chair") private User user; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn private Room room; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } public Room getRoom() { return room; } public void setRoom(Room room) { this.room = room; } }
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 @Entity public class Room { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @OneToMany(mappedBy = "room") private List<Chair> chairs = new ArrayList<>(); private String name; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public List<Chair> getChairs() { return chairs; } public void setChairs(List<Chair> chairs) { this.chairs = chairs; } public String getName() { return name; } public void setName(String name) { this.name = name; } }

When I try to retrieve all user and sort by room name, I get only users which have chair and room, but users without chair and room are not retrieved:

1 2 3 4 5 6 //work wrong, cross joins Order order2 = cb.asc(root.get("chair").get("room").get("name")); query.orderBy(order2); users = entityManager.createQuery(query). setHint("javax.persistence.fetchgraph", entityGraph).getResultList();

The following sql output printed, which contains unexpected cross join filtering null values:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 select user0_.id as id1_2_0_, chair1_.id as id1_0_1_, room2_.id as id1_1_2_, user0_.chair_id as chair_id2_2_0_, chair1_.room_id as room_id2_0_1_, room2_.name as name2_1_2_ from User user0_ left outer join Chair chair1_ on user0_.chair_id=chair1_.id left outer join Room room2_ on chair1_.room_id=room2_.id cross join Room room4_ where chair1_.room_id=room4_.id order by room4_.name asc

Environment

h2, mariadb

Status

Assignee

Unassigned

Reporter

Sviataslau Apanasionak

Fix versions

None

Labels

backPortable

Backport?

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.4.3
5.2.17

Priority

Major