ConverterNotFoundException exception in hql spring-data-jpa-3.1.0

[teopapath]

I have a NamedQuery like:

@NamedQuery(
        name = exampleQuery,
        query = "select new Pojo("
                + "au.userId, au.status, r.name, r.code, au.creationDate) "
                + "from Au au "
                + "join Role r on r.code = au.code "
                + "where au.accountId = :accountId "
                + "and au.status <> 'PENDING' ")

When query is executed a ConverterNotFoundException is thrown.

If i change the query to

@NamedQuery(
        name = exampleQuery,
        query = "select new Pojo("
                + "au.userId, au.status, r.name, r.code, au.creationDate) "
                + "from Au au "
                + "join Role r on r.code = au.code "
                + "where au.accountId = :accountId "
                + "and au.status not in ('PENDING') ")

everything works as expected.
It seems that there is a problem in parsing this part: and au.status <> 'PENDING'
The same query was executing successfully in spring-boot 3.0.7

Stacktrace:

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [Pojo]
	at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:322) ~[spring-core-6.0.9.jar:6.0.9]
	at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:195) ~[spring-core-6.0.9.jar:6.0.9]
	at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:175) ~[spring-core-6.0.9.jar:6.0.9]
	at org.springframework.data.repository.query.ResultProcessor$ProjectingConverter.convert(ResultProcessor.java:305) ~[spring-data-commons-3.1.0.jar:3.1.0]
	at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.lambda$and$0(ResultProcessor.java:233) ~[spring-data-commons-3.1.0.jar:3.1.0]
	at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.convert(ResultProcessor.java:240) ~[spring-data-commons-3.1.0.jar:3.1.0]
	at org.springframework.data.repository.query.ResultProcessor.processResult(ResultProcessor.java:160) ~[spring-data-commons-3.1.0.jar:3.1.0]
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:151) ~[spring-data-jpa-3.1.0.jar:3.1.0]
	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:136) ~[spring-data-jpa-3.1.0.jar:3.1.0]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136) ~[spring-data-commons-3.1.0.jar:3.1.0]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120) ~[spring-data-commons-3.1.0.jar:3.1.0]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164) ~[spring-data-commons-3.1.0.jar:3.1.0]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143) ~[spring-data-commons-3.1.0.jar:3.1.0]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.9.jar:6.0.9]
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:77) ~[spring-data-commons-3.1.0.jar:3.1.0]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.9.jar:6.0.9]
2.$Proxy262.exampleQuery(Unknown Source) ~[?:?]

[gregturn]

Last I checked, DTO via construction in the query requires a FQDN class definition. Does your POJO definition really NOT have a package declaration? If so, I'd recommend testing out if putting it into a package and then putting that into the query resolves the issue.

I've also run this query through the HQL parser, and our parser has no issue with the <> operator. It along with NOT IN() both pass our parser. And I don't see the parser throwing any sort of exception as if if did.

You may also want to turn on Hibernate logging to see more of what's happening as well.

[teopapath]

Yes indeed, i just put pojo in the example for the sake of simplicity. The actual code of course contains a fully qualified name. I can update the code snippet but this is not the issue.

After some debugging it seems that the query executes successfully and correct results are returned. It seems that the error is in the result processing.

[gregturn]

Can you post your repository definition along with the domain definition?

[teopapath]

Repository:

@Repository
public interface AuthorityRepository extends JpaRepository<Authority, Long>, AuthorityCustomRepository {

    @Transactional(readOnly = true)
    @Query(name =  "exampleQuery")
    List<AuthorityRoleDTO> query(Long accountId);

}

Domain:

@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
@Entity
@EntityListeners({AuditingEntityListener.class})
@NamedQuery(
        name = "exampleQuery",
        query = "select new api.authorization.core.domain.dto.AuthorityRoleDTO("
                + "au.userId, au.status, r.name, r.code, au.creationDate) "
                + "from Authority au "
                + "join Role r on r.code = au.code "
                + "where au.accountId = :accountId "
                + "and au.status <> 'PENDING' ")
public class Authority implements GrantedAuthority {
  @Id
    @SequenceGenerator(name = "au_authority_id_generator", sequenceName = "au_authority_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "au_authority_id_generator")
    private Long id;

    @EqualsAndHashCode.Include
    @NotNull
    @Column(name = "user_id")
    private String userId;

    @EqualsAndHashCode.Include
    @Column(name = "code")
    private String code;

    @NotNull
    @Enumerated(EnumType.STRING)
    @Column(name = "status")
    private AuthorityStatus status;

    @EqualsAndHashCode.Include
    @Column(name = "account_id")
    private Long accountId;

    @Column(name = "uuid")
    private String uuid;

    @NotNull
    @Column(name = "creation_date")
    @CreatedDate
    private LocalDateTime creationDate;

    @NotNull
    @Column(name = "created_by")
    private String createdBy;

    @Builder.Default
    @OneToMany(mappedBy = "authority", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    private List<AuthorityPermission> authorityPermissions = new ArrayList<>();

    public void addPermission(AuthorityPermission authorityPermission) {
        if (authorityPermissions == null) {
            authorityPermissions = new ArrayList<>();
        }
        authorityPermission.setAuthority(this);
        authorityPermissions.add(authorityPermission);
    }

    @Override
    public String getAuthority() {
        return getCode();
    }

}

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class AuthorityRoleDTO {

    private String userId;
    private AuthorityStatus authorityStatus;
    private String roleName;
    private String roleCode;
    private LocalDateTime creationDate;
}

[gregturn]

I can't see anything obvious in the code listings you have provided.

Is there perhaps some way to build a reproducer, staged on github, ideally using https://start.spring.io, H2 (or Testcontainers for PostGreSQL, etc.), and the version of Spring Boot aligned with your issue?

[teopapath]

spring boot version is 3.1.0. I will try to find some time to implement a test project

[teopapath]

created a test project that reproduces the error in https://github.com/teopapath/spring-boot-error. Changing the spring-boot version makes the test successful.

[gregturn]

Simply switching from:

    @Transactional(readOnly = true)
    @Query(name =  Authority.NAMED_QUERY_FIND_NON_PENDING_AUTHORITY_ROLE_LIST)
    List<AuthorityRoleDTO> findNonPendingAuthorityRoleList();

to

    @Transactional(readOnly = true)
    @Query("select new com.example.demo.AuthorityRoleDTO("
            + "au.userId, au.status, r.name, r.code, au.creationDate) "
            + "from Authority au "
            + "join Role r on r.code = au.code "
            + "where au.status <> 'PENDING' ")
    List<AuthorityRoleDTO> findNonPendingAuthorityRoleList();

seems to solve the problem and make the test go green. So apparently the issue has to do with named queries vs. directly provided ones.

[teopapath]

We have tons of such queries in our application and is impossible to test them (or change) all. Will a fix be provided for this?
Moreover i think that the root-cause may be more complicated because if the query is changed to "where au.status not in ('PENDING') " it works as expected.

[gregturn]

@teopapath Sorry, I wasn't suggesting that you alter your app. I'm simply tracking that this might be where the discrepancy lies. I'm trying to track down precisely where it diverges.

[gregturn]

I figured it out. It's a little pesky. You see the @NamedQuery annotation (from JPA) feeds your defined query into Spring Data JPA's NamedQuery, which executes the following statement to extract the HQL from Hibernate:

String queryString = extractor.extractQueryString(query);

This taps into Hibernate directly, asking for the query. Apparently Hibernate when parsing:

@NamedQuery(
        name = Authority.NAMED_QUERY_FIND_NON_PENDING_AUTHORITY_ROLE_LIST,
        query = "select new com.example.demo.AuthorityRoleDTO("
                + "au.userId, au.status, r.name, r.code, au.creationDate) "
                + "from Authority au "
                + "join Role r on r.code = au.code "
                + "where au.status <> 'PENDING' ")

...transforms <> some sort of internal NOT_EQUALS operator, which then gets re-rendered into != (not under our control). This re-rendered query is later run through our HQL parser for any additional processing (applied sorts, etc.) before getting sent to the entity manager.

However, in Spring Data JPA 3.1.0 (part of Spring Boot 3.1.0), we didn't have proper support for != within our own HQL parser. That would also explain why it fails on <> but passes when using the semantically equivalent NOT IN ('PENDING').

This was patched with #2970 via 6d56a25. The good news is that this Friday, there is a service release scheduled for Spring Data 2023.0.1. When that goes out, you can apply:

<properties>
    <spring-data-bom.version>2023.0.1</spring-data-bom.version>
</properties>

...and pick up that patch.

You can also choose to wait until the 22nd for Spring Boot's scheduled release of 3.1.1, which should pick up Spring Data's service release.

Or you can choose to jump to snapshots, as you've already seen.

Since this issue simply duplicates #2970, I'm going to close it.

[teopapath]

@gregturn it seems that the issue still exists in 3.1.2. Test case can be found in error2990_2. Still works in 3.0.x release. Opened #3085 for this

[gregturn]

Well, the scenario this ticket as closed against was lack of support for <> operator, which now operates.

The other scenario where the NOT IN isn't working for an EnumMap-based relationship sounds like a different scenario, as tracked by #3085.