Getting "Multi valued paths are only allowed for the member of operator" after upgrading to 6.3.1

Hibernate ORM
1

Hey!

I just upgraded Hibernate ORM from 6.2.9 to 6.3.1 and I am getting this pretty weird validation error:

Caused by: org.hibernate.query.SemanticException: Multi valued paths are only allowed for the member of operator
	at org.hibernate.query.sqm.internal.TypecheckUtil.assertComparable(TypecheckUtil.java:329) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.sqm.tree.predicate.SqmInListPredicate.implyListElementType(SqmInListPredicate.java:139) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
Caused by: org.hibernate.query.SemanticException: Multi valued paths are only allowed for the member of operator

	at org.hibernate.query.sqm.tree.predicate.SqmInListPredicate.<init>(SqmInListPredicate.java:61) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInPredicate(SemanticQueryBuilder.java:2612) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInPredicate(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.grammars.hql.HqlParser$InPredicateContext.accept(HqlParser.java:6046) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]

I have this query:

  @Query(
    """
    select distinct sp 
    from CloudSubscriptionPlan sp 
       left join CloudSubscription s on s.activePlan = sp 
    where sp.id = :id 
       and (sp.public = true or :organizationId in (sp.forOrganizationIds) or s.organizationId = :organizationId)
    """
  )
  fun findForOrganization(id: Long, organizationId: Long): CloudSubscriptionPlan?

And the CloudSubscriptionPlan entity looks like this:

...

@Entity
@Table(name = "subscription_plan", schema = "billing", indexes = [Index(columnList = "free")])
class CloudSubscriptionPlan(
...
  @ElementCollection
  @CollectionTable(
    schema = "billing",
    name = "subscription_plan_for_organization_ids",
    joinColumns = [JoinColumn(name = "subscription_plan_id")]
  )
  override var forOrganizationIds: MutableSet<Long> = mutableSetOf(),
...
) : StandardBillingModel(), SubscriptionPlan {}
2

It works after downgrading to 6.2.13.Final.

3

Assuming that forOrganizationIds is a plural attribute i.e. collection, your predicate should rather be :organizationId member of sp.forOrganizationIds instead of :organizationId in (sp.forOrganizationIds). Pervious versions of Hibernate probably added a join to the main query for this which changes result cardinality.

4

I ran into a similar issue using version 6.6.11.Final.

I have an entity A, which has a @ManyToMany relation with entity B. The relation is implemented as a two-column table, with uuids of both entities.
Entity A also a has an @ElementCollection field (let’s call it bIds), which is mapped to the same many-to-many table, it is a Set<UUID>, containing the uuids of related B entities.

I need to retrieve entities A using criteria API with these conditions:

  1. they must have a relation to at least one of the Bs with ids from the supplied Collection<UUID> bIds
  2. their bIds must be fetched

Now, the straightforward option of root.get("bIds").in(bIds) doesn’t work due to the the exception from above.
Replacing get with a join works, but since you also need a fetch, you end up with 2 of the same join, which, at least in my case, wrecked havoc on query plan and performance (and is also just ugly).
What I ended up doing is a fetch which is then casted to a Join, so I only get a single join. I undestand that this is a shortcoming of the JPA itself, but at least before this change you didn’t have to scratch your head dealing with either two joins or casts, you could traverse the attribute like any other.
Also, I’m not sure if you can use member of with a multi-valued argument, like you can do with in operator, so this looks like a functional regression.

5

Here is how you can do that. Please ask questions in a new topic on Discourse the next time.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<A> query = cb.createQuery(A.class);
Root<A> root = query.from(A.class);
root.fetch("bManyToMany");
Subquery<Integer> subquery = query.subquery(Integer.class);
subquery.select(cb.literal(1));
Join<?,B> b = subquery.from(subquery.correlate(root).join("bManyToMany"));
subquery.where(cb.equal(b.get("id"),uuid));
query.where(cb.exists(subquery));

List<A> results = entityManager.createQuery(query).getResultList();

This is roughly equivalent to this HQL:

from A a
join fetch a.bManyToMany
where exists(
  select 1
  from a.bManyToMany b
  where b.id = :uuid
)