Interface-based Projections - Generate inner join instead of left join [DATAJPA-1418]

[spring-projects-issues]

Frederic Goulet opened DATAJPA-1418 and commented

Since we upgraded our project to Spring Boot 2.0.3.RELEASE, Recursive (or nested) interface projection generate inner joins.

In Spring Boot 2.0.2.RELEASE, the exact same code generate left outer join.

The resultset is not the same thus breaking our application.

 

  

---

Affects: 2.0.9 (Kay SR9)

Issue Links:

• DATAJPA-1434 Spring Data Jpa Projection Fetches all the columns despite of defined fields

Referenced from: pull request #294

Backported to: 2.1.2 (Lovelace SR2), 2.0.12 (Kay SR12)

5 votes, 6 watchers

[spring-projects-issues]

Thomas Maxwell commented

Noticed the same in our codebase after upgrading to 2.0.4.RELEASE

[spring-projects-issues]

Réda Housni Alaoui commented

According to my tests the issue appeared between spring-data-jpa 2.0.7.RELEASE and 2.0.8.RELEASE

[spring-projects-issues]

Réda Housni Alaoui commented

The behaviour changed with 876669fc54cf2484d2f522da5f8be791cce8fc38 but IMO the issue comes from Hibernate, not Spring Data.

With the following test:

 @Transactional
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = ProjectionsIntegrationTests.Config.class)
public class ProjectionJoinIntegrationTests {

    @Inject
    private UserRepository userRepository;

    @Test
    public void test() {
        User user = userRepository.save(new User());

        UserProjection projection = userRepository.findById(user.getId(), UserProjection.class);
        assertThat(projection).isNotNull();
        assertThat(projection.getId()).isEqualTo(user.getId());
        assertThat(projection.getAddress()).isNull();
    }

    private static class UserProjection {

        private final int id;
        private final Address address;

        public UserProjection(int id, Address address) {
            this.id = id;
            this.address = address;
        }

        public int getId() {
            return id;
        }

        public Address getAddress() {
            return address;
        }
    }

    public interface UserRepository extends CrudRepository<User, Integer> {

        <T> T findById(int id, Class<T> projectionClass);
    }

    @Table(name = "ProjectionJoin_User")
    @Entity
    static class User {

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Access(value = AccessType.PROPERTY)
        private int id;

        @OneToOne(cascade = CascadeType.ALL)
        private Address address;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public Address getAddress() {
            return address;
        }

        public void setAddress(Address address) {
            this.address = address;
        }

    }

    @Table(name = "ProjectionJoin_Address")
    @Entity
    static class Address {

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Access(value = AccessType.PROPERTY)
        private int id;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }
    }
}

Hibernate generates an inner join leading to no result returned since adress is null:

select
  projection0_.id as col_0_0_,
  projection0_.address_id as col_1_0_ 
from
  ProjectionJoin_User projection0_ 
inner join
  ProjectionJoin_Address projection1_ 
  on projection0_.address_id=projection1_.id 
where
  projection0_.id=? 

IMO, this inner join is useless since the only address fetched attribute is id which is present in the User table

[spring-projects-issues]

Réda Housni Alaoui commented

I created an issue in Hibernate => https://hibernate.atlassian.net/browse/HHH-12999

[spring-projects-issues]

Réda Housni Alaoui commented

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

By default, when a foreign entity, without further sub attribute, is involved, Hibernate generates an inner join on the table of the foreign entity. Hibernate internals do this before processing the EntityGraph. So even when the final SQL query only fetches the foreign entity ID hold by the primary table, it joins on the foreign table. I don't think Hibernate is going to change this behaviour before 6.0.

The current bug was introduced by DATAJPA-1238 fix. The goal of DATAJPA-1238 was to avoid a useless left outer join when a foreign entity was involved in the query predicate (findByX where X is an entity). Its fix removed the outer join when the navigated foreign entity was a leaf, regardless of the location of the property representing the entity.

My pull request creates a distinction between properties navigated inside the select clause and other properties (i.e. navigated in the predicate). If the entity property is a leaf outside a select clause, DATAJPA-1238 fix is kept, no outer join is generated. If the property is a leaf inside a select clause, an outer join is created as it was before DATAJPA-1238 fix

[spring-projects-issues]

Jens Schauder commented

Thanks for creating the Hibernate issue and the PR. 

I don't think Hibernate is going to change this behavior before 6.0\

Why do think that? At least as long I don't have a statement from the Hibernate team that they don't intend to fix it/ don't consider it a bug I'd rather not put a workaround on our side into place

[spring-projects-issues]

Réda Housni Alaoui commented

Jens Schauder, I said that because of this answer of MIhalcea Vlad on twitter:

https://twitter.com/vlad_mihalcea/status/1047106358062014466

That part will change massively in 6.0, so you are better off waiting for that release. There's not too much documentation about HQL parsing. You have to study the code to understand how it works.

[spring-projects-issues]

Jens Schauder commented

Doesn't look like we are going to get the Hibernate team convinced.

In order to get the specification clarified I added an issue with the spec: jakartaee/persistence#189

And I'll reconsider your PR as a workaround