Sort problem with mix of optional and non optional associations, null values are filtered [DATAJPA-1572]

[spring-projects-issues]

Sviataslau Apanasionak opened DATAJPA-1572 and commented

Given following entities:

@Entity
public class User {

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


    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn
    private Room room;
}

User has optional @ManyToOne.

@Entity
public class Room {

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

    @OneToMany(mappedBy = "room")
    private List<User> users = new ArrayList<>();


    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn
    private Floor floor;
}

Room has non optional @ManyToOne (this optional attribute creates issue).

@Entity
public class Floor {

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

    private String number;

    @OneToMany(mappedBy = "floor")
    List<Room> rooms;
}

Then I create two users, one has room, second doesn't have.

I try to get users sorted by floor number:

Sort sort = new Sort(new Sort.Order("room.floor.number"));
List<User> users = userRepository.findAll(sort);

Assert.assertEquals(2, users.size());

Expected that list size would be 2, but it's 1.

The following query is generated:

select
       user0_.id as id1_2_0_,
       room1_.id as id1_1_1_,
       floor3_.id as id1_0_2_,
       user0_.room_id as room_id2_2_0_,
       room1_.floor_id as floor_id3_1_1_,
       room1_.name as name2_1_1_,
       floor3_.number as number2_0_2_ 
   from
       user user0_ 
   left outer join
       room room1_ 
           on user0_.room_id=room1_.id 
   left outer join
       floor floor3_ 
           on room1_.floor_id=floor3_.id cross 
   join
       floor floor2_ 
   where
       room1_.floor_id=floor2_.id 
   order by
       floor2_.number asc

The query contains unexpected cross join which filter null values.

Expected behavior: if root entity has optional association, for all entities in path only left join must be used inspite of having non optional associations

---

Affects: 1.11.22 (Ingalls SR22), 2.1.9 (Lovelace SR9)

Attachments:

• sorting-issue-optional.zip (6.67 kB)

Referenced from: pull request https://github.com/spring-projects/spring-data-jpa/pull/391

[spring-projects-issues]

Sviataslau Apanasionak commented

I tried to fix issue myself. I created pull request:

https://github.com/spring-projects/spring-data-jpa/pull/391

[spring-projects-issues]

Prateek commented

Is this being merge ? Can we expect fix of it now ?

[spring-projects-issues]

Jens Schauder commented

Prateek The PR is not reviewed yet as you can see in the PR at GitHub

[manousos]

On 19/11/2020 the commit 32b6163c49b7667e0d86d52022f9e17bd555423a resolves the issue.

When execute the attached integration test (@Sviataslau Apanasionak - sorting-issue-optional.zip) the test is passed and the generated SQL statement is the following (as expected)

select
        user0_.id as id1_2_0_,
        room1_.id as id1_1_1_,
        floor2_.id as id1_0_2_,
        user0_.room_id as room_id2_2_0_,
        room1_.floor_id as floor_id3_1_1_,
        room1_.name as name2_1_1_,
        floor2_.number as number2_0_2_ 
    from
        user user0_ 
    left outer join
        room room1_ 
            on user0_.room_id=room1_.id 
    left outer join
        floor floor2_ 
            on room1_.floor_id=floor2_.id 
    order by
        floor2_.number asc

I upgrade the spring-boot version in earlier version and all works fine. So, this bug could be close as resolved.

[gregturn]

Resolved via #2111.