Projections - query using an interface projection returns all fields if there are collections in the projection

[MFDEV17]

There are several entities in the project, such as User and enum Role, which is used in the User entity as @ElementCollection.

User entity

public class User {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;

    @Column(unique = true, nullable = false)
    private String username;

    @Column(unique = true, nullable = false)
    private String email;

    @Column(nullable = false, length = 80)
    private String password;

    @Column(nullable = false)
    private Boolean isActive = false;

    @ElementCollection(fetch = LAZY)
    private Set<Role> roles = new HashSet<>(Set.of(USER));
}

Role enum

public enum Role implements GrantedAuthority {
    USER, ADMIN;
    
    @Override
    public String getAuthority() {
        return name();
    }
}

For the User entity, I created a special projection that includes the following fields, including the role collection:

public interface UserProjection {
    String getUsername();
    String getPassword();
    Set<Role> getRoles();
}

And a JPA repository with a method that finds by username the user using this projection:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @EntityGraph(attributePaths = {"roles"})
    Optional<UserProjection> findUserByUsername(String username);
}

When I call this method, I run into the problem that the query contains all fields, including those not specified in the projection:

select
        user0_.id as id1_1_,
        user0_.email as email2_1_,
        user0_.is_active as is_activ3_1_,
        user0_.password as password4_1_,
        user0_.username as username5_1_,
        roles1_.users_id as users_id1_2_0__,
        roles1_.roles as roles2_2_0__ 
    from
        users user0_ 
    left outer join
        users_roles roles1_ 
            on user0_.id=roles1_.users_id 
    where
        user0_.username=?

But if I remove the collection from the projection, the query starts working correctly:

select
        user0_.username as col_0_0_,
        user0_.password as col_1_0_ 
    from
        users user0_ 
    where
        user0_.username=?

Can you please tell me what could be the problem? I'm not very experienced in using Spring Data JPA, and I can't figure out if this is a bug or some implementation feature that I missed. I couldn't find any answers in the documentation. Thanks

[schauder]

We try to limit fields selected as much as possible for interface projections.
But when collections are involved there is no way AFAIK to get the collections instantiated without querying for actual entities, which in turn will select all the fields.

I don't see a way how we can improve on this without reimplementing the mapping logic of the JPA implementation at hand, and I don't think we want to get into that.

[MFDEV17]

Gotcha. Thanks for the feedback.

[radarsh]

Hi @schauder @mp911de can I confirm if we still cannot get this to work if a projection declares methods with collection return types?

[radarsh]

What I discovered is that if I change the type of the collection methods in projection to be Object, it no longer does a full select.

public interface UserProjection {
    String getUsername();
    String getPassword();
    Object getRoles();

    default Set<Role> getRoles() {
        return getRoles() instanceof Set ? (Set<Role> getRoles()) : Set.of();
    }
}

I can live with this.