Exception with CTE and Spring Boot 3.2.x on subquery join

[mgeuer]

We are facing a problem with spring boot 3.2.x or newer when we use CTEs within a joinOn().on().in().fromSubqery() struct:

java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: Cannot compare tuples of different lengths

	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:143)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:167)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:173)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:886)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:143)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:320)
	at jdk.proxy2/jdk.proxy2.$Proxy120.createQuery(Unknown Source)
	at com.blazebit.persistence.impl.AbstractCommonQueryBuilder.getTypedQuery(AbstractCommonQueryBuilder.java:2852)
	at com.blazebit.persistence.impl.AbstractQueryBuilder.getQuery(AbstractQueryBuilder.java:42)
	at com.blazebit.persistence.impl.AbstractQueryBuilder.getResultList(AbstractQueryBuilder.java:47)

With spring boot 3.2 a new hibernate version 6.6.11.Final is used where TypecheckUtil now checks the comparable state of tuple length (line 389). I have provided some example code showing the problem while executing the ServiceIT test.

blazepersistence.zip

[mgeuer]

Hi everyone, any news on this problem? We still get the exception.

[MarioTrappein]

I can reproduce this exception. Updating to the newest spring boot release (3.5.3) doesn't seem to fix it. Any updated on this @beikov ?

[jwgmeligmeyling]

You're comparing an composite identifier field with an entity (KeyVerCte). I don't think this was ever intentionally supported? I would expect that you have to compare either the entity association (B) or the identifiers (BPk) directly.

[beikov]

I guess it was just by "luck" that this worked before. You shouldn't use .on("bs.id").in().select("cte.key")..select("cte.ver") but rather use an exists subquery e.g.

  public List<A> loadAWithBs() {
    return cbf.create(em, A.class)
        .from(A.class)
        .joinOn("bs", "b", JoinType.INNER)
        .onExists()
            .fromSubquery(KeyVerCte.class, "cte")
                .bind("key").select("kvr.key")
                .bind("ver").select("kvr.ver")
                .fromSubquery(KeyVerRkCte.class, "kvr")
                    .from(B.class, "ba")
                    .bind("key").select("ba.id.key")
                    .bind("ver").select("ba.id.version")
                    .bind("rk").select("1")
                .end()
                .where("kvr.rk").eqLiteral(1)
            .end()
            .select("1")
            .where("cte.key").eqExpression("b.id.key")
            .where("cte.ver").eqExpression("b.id.version")
            .end()
        .end()
        .getResultList();
  }