带位置参数的JPQL传入空集合时触发antlr解析异常问题

问题情况 #

在Spring Data JPA中实现了筛选SocialEvent的JPQL查询方法filter2Main，采用位置参数传参。其中Set<Long>类型的connecteValuesIds对应位置参数?8：

• 集合包含元素时查询正常
• 传入空集合时抛出antlr.NoViableAltException异常
• 尝试用COALESCE(?8, NULL)处理空集合无效，且不想修改位置参数的实现方式

相关代码 #

@Query(value =  "SELECT DISTINCT e from SocialEvent e "
            + "join e.multiPropsValuesSet m "
            + "join e.multiPropsValuesSet c "
            + "join e.multiPropsValuesSet a "
            + "WHERE ( "
//          +"e IS NOT NULL AND"//trying to add condition - NOT HELP
//          + "m in ?1"
            + "((m in ?1   OR COALESCE(?1, NULL) is null )"     //Prblem - this don't working but the other 
            
//          +"  (COALESCE(?1) is null  OR m in ?1 )"//won't working too 
//          + " AND   c in ?2 "//the good - if the parameter empty throw 500.. and about the gap 
            +" AND  (COALESCE(?2, NULL) is null  OR c in ?2 ) "//work
//          +" AND   c in ?2 ) "//
            + "AND  (COALESCE(?3, NULL) is null  OR a in ?3 )) "
//          + " OR (COALESCE(?8, NULL) is  null OR m.id in (?8)   ) "//not working when empty
//          + " OR ( m.id in (?8)   ) "
//          + " OR (?#{7} is not empty and m.id in (?8)   ) "//coalesce(:unitNames)
//          +"OR (COALESCE(#connected, NULL )is not null and m.id in (?8)   ) "//not compiling
//          +"OR (COALESCE(:connected, NULL )is not null and m.id in (?8)   ) "//not compiling
            +"OR (COALESCE(?8, NULL )is not null and m.id in (?8)   ) "//the same error:"antlr.NoViableAltException: unexpected token: ,"



//          + " AND  a in ?3"           
            + " ) "
            + " AND "
            + "((e.fromAge BETWEEN ?4 AND ?5) "
            + "OR (e.toAge BETWEEN ?4 AND ?5)"
            + "OR (?4 BETWEEN e.fromAge AND e.toAge )) "
            + "AND (e.date BETWEEN ?6 AND ?7) "
            + "ORDER BY e.date ASC"   /*, nativeQuery=true - cause errror*/)//--- work ? 
    List<SocialEvent> filter2Main(
            List <MultiPropValue> eventTypes,
            List <MultiPropValue> areas, List <MultiPropValue> jewLvlKeep,
            int fromAge, int toAge,
            Date from , Date to,
            @Param ("connected")Set<Long> connecteValuesIds);//was set

异常信息 #

antlr.NoViableAltException: unexpected token: ,
    at org.hibernate.hql.internal.antlr.HqlBaseParser.exprList(HqlBaseParser.java:4009) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    ...

可行解决方案 #

方案1：使用JPQL原生IS EMPTY判断空集合 #

直接替换原有的COALESCE逻辑，改用JPQL原生支持的集合空判断：

"OR (?8 IS EMPTY OR m.id IN ?8) "

空集合时会跳过IN查询分支，避免antlr解析报错；非空时正常执行匹配逻辑。

方案2：提前将空集合转为null #

在调用方法前，把空的connecteValuesIds替换为null，然后在JPQL中判断：

"OR (?8 IS NULL OR m.id IN ?8) "

调用时的处理示例：

Set<Long> processedIds = connecteValuesIds.isEmpty() ? null : connecteValuesIds;
socialEventRepository.filter2Main(eventTypes, areas, jewLvlKeep, fromAge, toAge, from, to, processedIds);

方案3：嵌入SpEL表达式（兼容位置参数） #

Spring Data JPA支持在JPQL中嵌入SpEL，位置参数的SpEL索引从0开始（第8个参数对应索引7），写法如下：

"OR (?#{#empty ? 1 : 0} = 1 OR m.id IN ?8) "

集合为空时SpEL返回1，条件成立跳过IN查询；非空时执行匹配逻辑。

关键原因说明 #

COALESCE无法处理空集合是因为：空集合在JPQL解析时会被转换为()，而COALESCE无法识别这种语法结构，直接触发antlr解析器报错。使用JPQL原生集合空判断或提前转null，才能从根源避免该问题。

内容的提问来源于stack exchange，提问作者lingar