JPA Custom Query with Nullable IN Clause

June 28, 2024

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

null
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?