Projection fails when using subquery [DATAJPA-1713]

[spring-projects-issues]

Manu VIDAL opened DATAJPA-1713 and commented

When using a subquery in query defined with @Query, JPA projection fails.
Return type is not of projection class but

org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap**

Example :

@Entity
public class Employee {
   @Id
   @GeneratedValue(strategy=GenerationType.AUTO)
   private Long id;
   private String firstName;
   private String lastName;
   private Integer salary;

   // Constructors, getters and setters
}

Projection class :

public interface EmployeeProjection {

    public Long getId();

    public String getFirstName();
 
    //@Value("#{target.firstName + ' ' + target.lastName}") 
    //String getFullName();
}

Repo :

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
   @Query("select c from Employee c where c.salary >= "
          //+ "500"
          + "(select avg(salary) from Employee )"
   )
   List<EmployeeProjection> findProjections();
}

When using condition "c.salary >= 500", method returns a correct result, i.e. a List<EmployeeProjection>.

But when using a subquery "c.salary >= (select avg(salary) from Employee)", method returns a list of AbstractJpaQuery$TupleConverter$TupleBackedMap.

On this object, calling any getter returns null.
Using @Value (open projection) throws an exception.

Example sources are attached.
Thanks in advance.

 

---

Affects: 2.2.6 (Moore SR6)

Attachments:

• demo.zip (4.51 kB)
• demo2.zip (5.76 kB)

Referenced from: pull request #420

[spring-projects-issues]

Jens Schauder commented

Thanks for raising the issue. Based on your reproducer I was able to add a test demonstrating the issue.

I haven't checked, but I assume this is due to the parser not being able to properly handle the subselect and therefore thinking the select returns single fields.

This might actually be a valid workaround: instead of returning an entity: return the fields of the entity required for the projection.

[spring-projects-issues]

Manu VIDAL commented

Ok, thanks Jens Schauder for your quick response.

I'v tested this Query : @Query("select c.id as id, c.firstName as firstName from Employee c [...]") and it works.
But i have to alias all the properties corresponding to the projection.

I've however another problem : if I have a nested projection, i.e. a @onetomany Set property on class Employee, and a List getDepartments() in EmployeeProjection, how can I write my Query ?

I point out that it works if @Query is "select c from Employee c"

Thanks

[spring-projects-issues]

Jens Schauder commented

If you want to create a complex object from a SQL query with JPA, you have to use ResultSet mapping. https://thoughts-on-java.org/result-set-mapping-basics/

[spring-projects-issues]

Manu VIDAL commented

Sorry Jens Schauder I think I expressed myself poorly. I've added a second project demo2.zip to illustrate my test case.

First method of repository works fine and retrieves 1 employee and its 2 jobs.

Second method retrieves bad type and wrong numbers of results.
In debug, we can see that tree representation of result is quite strange : data is present but duplicated and hard to extract from TupleBackedMap.

So ... is there a workaround for this precise case ?

Thanks again for your help

[jamesdh]

Likewise, seeing something similar when using a Postgres jsonb function to try and render a jsonb array of objects as a list of entity types, e,g...

@Query(
    nativeQuery = true,
    value = """
        SELECT
            x.path,
            x.base64
        FROM
            claim c,
            jsonb_to_recordset(c.files) as x(path varchar, base64 varchar)
        WHERE c.id = :id
    """)
List<SupportFile> findFiles(@RequestParam UUID id);

[gregturn]

If this involves native queries and JSONB functions on the database provider, this may have gone into something too complex for Spring Data JPA to support. Your best best moving forward may be to implement a custom implementation. Check out https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations for more details on how to hook such a solution into your repository.

[gregturn]

The original reporter on this ticket had a problem with:

@Query("select c from Employee c where c.salary >= (select avg(salary) from Employee )")
List<EmployeeProjection> findProjections();

This is non-native query that involves a subselect and projections. @schauder coded a simple reproducer of the same issue that fits into our existing test machinery:

// DATAJPA-1713
@Query("select u from User u where firstname >= (select Min(u0.firstname) from User u0 )")
List<NameOnly> findProjectionBySubselect();


@Test // DATAJPA-1713
public void selectProjectionWithSubselect() {

	List<UserRepository.NameOnly> list = userRepository.findProjectionBySubselect();
	assertThat(list).isNotEmpty() //
			.allSatisfy(no -> {

				assertThat(no.getFirstname()).describedAs("firstname").isNotNull();
				assertThat(no.getLastname()).describedAs("lastname").isNotNull();
			});
}

This test scenario is overcome by the new query parser and passes.

NOTE: This ticket was closed due to an unrelated native query, for which the query parser does NOT get applied, so that's why it's reopened.