'JPQL query Exception unexpected AST node: {vector}

dears, I have a problem working with JPQL query, when I work with IN and a List(Long) I get an error, but no always

this is the model (is minimal model for look the relationships, without all columns)

and if I do this query in mi repo works fine:

@Query(value = "SELECT a FROM Artist a WHERE a.idArtist IN(SELECT a.idArtist FROM ArtistStyle x "
            + "INNER JOIN x.style s " 
            + "INNER JOIN x.artist a " 
            + "WHERE s.idStyle IN (:arrayStyles))")
    public List<Artist> FilterArtistWithStyles(List<Long> arrayStyles);

but if I do this query I get and error:

@Query(value = "SELECT a FROM Artist a WHERE a.idArtist IN(SELECT a.idArtist FROM ArtistStyle x "
            + "INNER JOIN x.style s " 
            + "INNER JOIN x.artist a " 
            + "INNER JOIN a.country c " 
            + "INNER JOIN a.aal1 a1 "
            + "INNER JOIN a.aal2 a2 " 
            + "INNER JOIN a.aal3 a3 " 
            + "WHERE (:arrayStyles IS NULL OR s.idStyle IN (:arrayStyles)) "
            + "AND (:name IS NULL OR a.name like %:name%)" + "AND (:gender IS NULL OR a.gender = :gender) "
            + "AND (:experienceFrom IS NULL OR a.workStartYear <= :experienceFrom) "
            + "AND (:ratingFrom IS NULL OR a.ratingAccordance >= :ratingFrom) "
            + "AND (:delivery IS NULL OR a.delivery = :delivery) "
            + "AND (:exchange IS NULL OR a.exchange = :exchange) " + "AND (:courses IS NULL OR a.courses = :courses)"
            + "AND (:idCountry IS NULL OR c.idCountry = :idCountry)"
            + "AND (:idAal1 IS NULL OR a1.idAdministrativeAreaLevel1 = :idAal1)"
            + "AND (:idAal2 IS NULL OR a2.idAdministrativeAreaLevel2 = :idAal2)"
            + "AND (:idAal3 IS NULL OR a3.idAdministrativeAreaLevel3 = :idAal3)" + ")")
    public List<Artist> SearchArtistWithStyles(String name, List<Long> arrayStyles, Integer gender, Integer experienceFrom, Double ratingFrom, Integer delivery,
            Integer exchange, Integer courses, Long idCountry, Long idAal1, Long idAal2, Long idAal3,
            Pageable pageable);

this is the error: 

"status": 500,
"error": "Internal Server Error",
"message": "org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: {vector} [SELECT a FROM com.mushroomstudios.skink.entities.Artist a WHERE a.idArtist IN(SELECT a.idArtist FROM com.mushroomstudios.skink.entities.ArtistStyle x INNER JOIN x.style s INNER JOIN x.artist a INNER JOIN a.country c INNER JOIN a.aal1 a1 INNER JOIN a.aal2 a2 INNER JOIN a.aal3 a3 WHERE (:arrayStyles_0, :arrayStyles_1, :arrayStyles_2 IS NULL OR s.idStyle IN (:arrayStyles_0, :arrayStyles_1, :arrayStyles_2)) AND (:name IS NULL OR a.name like :name)AND (:gender IS NULL OR a.gender = :gender) AND (:experienceFrom IS NULL OR a.workStartYear <= :experienceFrom) AND (:ratingFrom IS NULL OR a.ratingAccordance >= :ratingFrom) AND (:delivery IS NULL OR a.delivery = :delivery) AND (:exchange IS NULL OR a.exchange = :exchange) AND (:courses IS NULL OR a.courses = :courses)AND (:idCountry IS NULL OR c.idCountry = :idCountry)AND (:idAal1 IS NULL OR a1.idAdministrativeAreaLevel1 = :idAal1)AND (:idAal2 IS NULL OR a2.idAdministrativeAreaLevel2 = :idAal2)AND (:idAal3 IS NULL OR a3.idAdministrativeAreaLevel3 = :idAal3)) order by a.name asc]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: {vector} [SELECT a FROM com.mushroomstudios.skink.entities.Artist a WHERE a.idArtist IN(SELECT a.idArtist FROM com.mushroomstudios.skink.entities.ArtistStyle x INNER JOIN x.style s INNER JOIN x.artist a INNER JOIN a.country c INNER JOIN a.aal1 a1 INNER JOIN a.aal2 a2 INNER JOIN a.aal3 a3 WHERE (:arrayStyles_0, :arrayStyles_1, :arrayStyles_2 IS NULL OR s.idStyle IN (:arrayStyles_0, :arrayStyles_1, :arrayStyles_2)) AND (:name IS NULL OR a.name like :name)AND (:gender IS NULL OR a.gender = :gender) AND (:experienceFrom IS NULL OR a.workStartYear <= :experienceFrom) AND (:ratingFrom IS NULL OR a.ratingAccordance >= :ratingFrom) AND (:delivery IS NULL OR a.delivery = :delivery) AND (:exchange IS NULL OR a.exchange = :exchange) AND (:courses IS NULL OR a.courses = :courses)AND (:idCountry IS NULL OR c.idCountry = :idCountry)AND (:idAal1 IS NULL OR a1.idAdministrativeAreaLevel1 = :idAal1)AND (:idAal2 IS NULL OR a2.idAdministrativeAreaLevel2 = :idAal2)AND (:idAal3 IS NULL OR a3.idAdministrativeAreaLevel3 = :idAal3)) order by a.name asc]",

finally, if I do that query, works fine too:

@Query(value = "SELECT a FROM Artist a " + "INNER JOIN a.country c " 
            + "INNER JOIN a.aal1 a1 "
            + "INNER JOIN a.aal2 a2 " 
            + "INNER JOIN a.aal3 a3 " 
            + "WHERE (:name IS NULL OR a.name like %:name%) "
            + "AND (:gender IS NULL OR a.gender = :gender) "
            + "AND (:experienceFrom IS NULL OR a.workStartYear <= :experienceFrom) "
            + "AND (:ratingFrom IS NULL OR a.ratingAccordance >= :ratingFrom) "
            + "AND (:delivery IS NULL OR a.delivery = :delivery) "
            + "AND (:exchange IS NULL OR a.exchange = :exchange) " + "AND (:courses IS NULL OR a.courses = :courses)"
            + "AND (:idCountry IS NULL OR c.idCountry = :idCountry)"
            + "AND (:idAal1 IS NULL OR a1.idAdministrativeAreaLevel1 = :idAal1)"
            + "AND (:idAal2 IS NULL OR a2.idAdministrativeAreaLevel2 = :idAal2)"
            + "AND (:idAal3 IS NULL OR a3.idAdministrativeAreaLevel3 = :idAal3)")
    public List<Artist> SearchArtist(String name, Integer gender, Integer experienceFrom, Double ratingFrom,
            Integer delivery, Integer exchange, Integer courses, Long idCountry, Long idAal1, Long idAal2, Long idAal3,
            Pageable pageable);

there are something bad in the second query? If you need more info like entity or other thing please let me know, thanks in advance

javahibernatejpql


Solution 1:[1]

problem was in this line

 "WHERE (:arrayStyles IS NULL OR s.idStyle IN (:arrayStyles)) "

I changed to, and works fine

+ "WHERE s.idStyle IN (:arrayStyles) "

Solution 2:[2]

I had to do a work around, creating a Boolean if the List has elements:

...

Boolean shouldValidateList = someList != null && !someList().isEmpty();

...

"Where :shouldValidateList = false or FieldInQuestion IN :someList"

Ps. In my case I had other filters in "where" condition, just the "IN" didn't work for me. But if it is the only filter, just "IN" works perfectly

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user3207976
Solution 2 Lino

Related Questions