JPA QuerySyntaxException: unexpected AST node: {vector} For In condition

Question

I am trying to use IN operation with @Query annotation with JPA. I am getting below error :-

    antlr.NoViableAltException: unexpected AST node: {vector}
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2112)

    org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: 
    unexpected AST node: {vector}
 [ [select o FROM Stock o where (:productlist_0_, :productlist_1_, :productlist_2_, :productlist_3_ is null or o.productsid IN (:productlist_0_, :productlist_1_, :productlist_2_, :productlist_3_) )

My Java code is below :-

@Query("SELECT o FROM Stock o  where (:productlist is null or o.productsid IN (:productlist) ) ")
List<Stockdiary> getAllStock(Pageable pageable, @Param("productlist") List<Products> productlist)

this works fine when i have only single item in productlist. but when i have multiple item in productlist is query comes out as below and error :-

select o FROM Stock o where (:productlist_0_, :productlist_1_, :productlist_2_, :productlist_3_ is null or o.productsid IN (:productlist_0_, :productlist_1_, :productlist_2_, :productlist_3_

I have look at this link but this workaround is not working for me. i tried passing :productlist with and without parentheses.

Answer 1

I had the same issue like you and the solution is quite easy. After work around I found that next variant is working fine:

@Query("select c from Cruise c where" +
        " (:categoryId is null or c.category.id = :categoryId)" +
        " and ((:portsIds) is null or c.port.id in (:portsIds))")
List<Cruise> findByRequestQuery(@Param("portsIds") List<Long> portsIds,
                                @Param("categoryId") Long categoryId);

As mentioned here: https://stackoverflow.com/a/24551530/6629515 you should add parentheses to collection parameter, for me it worked if add parentheses to each collection parameter mentions:

(:portsIds)

And the result SQL query where part looks like:

where (? is null or cruise0_.category_id=?) and ((? , ? , ?) is null or cruise0_.port_id in (? , ? , ?))

For Operand should contains 1 column issue

Please read here: https://stackoverflow.com/a/51958547/1522490

In short: use coalesce(:portsIds, null) is null