Limit selection of nested properties

[scordio]

I'm not sure if I should report this in spring-data-jpa or spring-data-commons. I apologize in advance if this isn't the right place.

I experienced the following issue on Spring Data JPA with Hibernate, initially with Spring Boot 2.7.14 and Db2 for z/OS, but also reproduced with the latest Spring Boot (both GA and snapshot) and H2.

In the Interface-based Projections section of both the Spring Data JPA and Spring Data Commons reference guides, there is an example on how to use such projections recursively:

interface PersonSummary {

  String getFirstname();
  String getLastname();
  AddressSummary getAddress();

  interface AddressSummary {
    String getCity();
  }
}

If I use this interface in a repository, like:

interface PersonRepository extends Repository<Person, UUID> {

  Collection<PersonSummary> findPersonSummaryByLastname(String lastname);
}

The following query is executed:

select p1_0.firstname,p1_0.lastname,p1_0.city,p1_0.street,p1_0.zip_code from person p1_0 where p1_0.lastname=?

where street and zip_code are also selected although they are not required.

This seems to contradict what is mentioned in the Closed Projections section:

If you use a closed projection, Spring Data can optimize the query execution, because we know about all the attributes that are needed to back the projection proxy.

As a workaround, if I remove the nested interface in PersonSummary and concatenate the property names:

interface PersonSummaryNoNested {

  String getFirstname();

  String getLastname();

  String getAddressCity();
}

the following query is executed:

select p1_0.firstname,p1_0.lastname,p1_0.city from person p1_0 where p1_0.lastname=?

Reproducer: PersonRepositoryTest

---

Is this a bug or is it by design?

In case the example with the nested interface is not meant to be considered as a closed projection, would it make sense to highlight this detail in the documentation?

[mp911de]

Currently, this behavior is by design as we use top-level properties for the selection. We could introspect the projection for nested projections to limit the selection.

For the time being, using inlined, concatenated properties makes sense. We can revisit this enhancement somewhere in the future.

[scordio]

Thank you for the feedback, @mp911de! Would you accept a small addition to the docs about it? I'd be happy to contribute.

Talking with colleagues, I realized this wasn't clear to many of them when looking at the PersonSummary example.

[christian-schlueter]

Would love to see this revisited in the future - if the nested entity has relationships itself that are not part of the projection, they will also get queried in separate queries, so this can result in a lot of wasted DB resources.