Hibernate NOT IN empty list evaluates to false

Question

I have the following hibernate query:

public long countHouseBoats(HouseBoat houseBoat, Type type, List<Long> excludedIds) {
   TypedQuery<Long> query = entityManager.createQuery("SELECT COUNT(o) FROM AquaticCity o " +
       "WHERE o.houseBoat = :houseBoat " +
       "AND o.chimneyType = :chimneyType " +
       "AND o.keel.manufacturer.id NOT IN (:excludedIds) " +
       "AND o.type.name IN (:types)", Long.class);
   query.setParameter("houseBoat", houseBoat);
   query.setParameter("chimneyType", ChimneyType.REGULAR);
   query.setParameter("excludedIds", excludedIds);
   query.setParameter("types", toStringList(List.of(type)));
   return query.getSingleResult();
}

It works as I expect if excludedIds is not empty. If it is empty then it is as though NOT IN <emptyList> evaluates to false rather than always evaluating to true as one would expect.

I am out of ideas why this happens. I have read around and some similar questions exists, but they are not really the same.

I'm about to resort to the obvious ugly workaround of checking if the list is empty and if it is, build the query without the excludedIds check.

What hibernate version do you use? What database? – SternK Feb 10 '21 at 13:02 — SternK, Feb 10 '21 at 13:02

score 1 · Accepted Answer · answered Feb 10 '21 at 13:11

As it's explained in the hibernate documentation:

The list of values can come from a number of different sources. In the constructor_expression and collection_valued_input_parameter, the list of values must not be empty; it must contain at least one value.

But actually the behavior related to in predicate processing was changed under HHH-8901. See additional details here. And probably that is why you get what you get.

Hibernate NOT IN empty list evaluates to false

1 Answers1