jpa

Why is JPA adding "IN (NULL)" in the generated SQL for a Predicate 'in clause'


We are using JPA via jakarta.jakartaee-api as a dependency in our project, so that we can use the CriteriaBuilder/CriteriaQuery features. An array of Predicates is used to create multiple conditions. One of the Predicates is a used to build an "IN" clause.

<dependency>
    <groupId>jakarta.platform</groupId>
    <artifactId>jakarta.jakartaee-api</artifactId>
    <version>11.0.0-M4</version>
    <scope>provided</scope>
</dependency>
...
predicates = new Predicate[] {
   ...    
   criteriaBuilder.in(root.get("zone").in(zoneList))
   ...
}
criteriaQuery.where(predicates);

The list "zoneList" contains two elements.

The generated SQL part for the "IN" clause contains IN (NULL), and therefore no rows are found in the DB.

AND (
      (
        t0.zone IN (?, ?)
      ) IN (NULL)
    )

Why is JPA adding IN (NULL) in the generated SQL?

Here's the complete Java code

public List<DBEntity> report() {
    EntityManager em = getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<DBEntity> criteriaQuery = cb.createQuery(DBEntity.class);
    Root<DBEntity> root = criteriaQuery.from(DBEntity.class);
    
    Predicate[] predicates =  new Predicate[] {
            cb.in(root.get("zone").in(Arrays.asList("Zone 1")))
    };
    
    criteriaQuery.where(predicates);
    criteriaQuery.select(root);
    TypedQuery<DBEntity> query = em.createQuery(criteriaQuery);
    return query.getResultList();
}

server in use: openliberty : 0.9.3

The DB driver is postgresql:

  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.4</version>
  </dependency>

Solution

  • The solution to create a Predicate object for an "IN CLAUSE" is as follow:

    Predicate[] predicates =  new Predicate[] {
            root.get("zone").in(Arrays.asList("Zone 1"))
    };