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.