JPA Custom Query with Nullable IN Clause

Question

I'm trying to write a custom query in my SpringBoot application using JPA, I have an IN clause with a list of values (list can also be null). I have written a query like this:

@Query( "SELECT new com.mypackage.model.CustomOutput( AVG(t.time) ) " +
        "FROM MyTable t WHERE " +
        "t.time IS NOT NULL AND t.updatedTime > :after AND t.updatedTime < :before " +
        "AND " +
        "( (:filterA) IS NULL OR t.columnA IN (:filterA) ) AND " +
        "( (:filterB) IS NULL OR t.columnB IN (:filterB) ) AND " +
        "( (:filterC) IS NULL OR t.columnC IN (:filterC) ) AND " +
        "( (:filterD) IS NULL OR t.columnD IN (:filterD) )"
)
CustomOutput findCustomOutput(
        @Param("filterA") List<String> filterA,
        @Param("filterC") List<String> filterC,
        @Param("filterB") List<String> filterB,
        @Param("filterD") List<String> filterD,
        @Param("after") Date after,
        @Param("before") Date before
);

All the filters - A,B,C,D can be a list of values or null, The query is working fine when any of the filters is

1. null
2. single value.

The query fails when there are more than one values (Eg: Filters - A,B,C are null, D is a list of two values). The exception is :

2024-06-28 12:32:32,543 [collectorsCluster_Worker-1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 920, SQLState: 42000
2024-06-28 12:32:32,544 [collectorsCluster_Worker-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-00920: invalid relational operator

2024-06-28 12:32:32,559 [collectorsCluster_Worker-1] ERROR c.c.d.job.servicenow.ServiceNowJob - Unexpected error occurred - 
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy231.findCustomOutput(Unknown Source)

The database is Oracle, Can someone please point to the issue in the syntax?

Answer 1

The issue is in (:filterA) IS NULL clause which fails for collections larger than one element.

See Check that a List parameter is null in a Spring data JPA query for proposed solutions

• using coalesce
• Using SpEL in the query (you seem to be using Spring, looking at the stack trace)

Another alternative is to use CriteriaAPI instead of JPQL and add list filters only if passed-in lists are not null.