Hibernate 6.2.0 Recursive CTE using Criteria API will throw `Already registered a copy: org.hibernate.query.sqm.tree.cte.SqmCteStatement` error

Hibernate ORM
1

Hibernate ORM - Issues - Hibernate JIRA indetail.

The HQL version of CTE Recursive query works fine:

with accountChainTable as(
  select child.id id, child.name name, child.parent.id parent_id from Account child where upper(child.name) like '%NAME%'
  union all
  select account.id, account.name, account.parent.id from Account account, accountChainTable chain
    where account.id = chain.parent_id
)
select c.id, c.name, c.parent_id from accountChainTable c

But the translated Criteria version will throw errors:

final HibernateCriteriaBuilder builder = session.getCriteriaBuilder();
final var query = builder.createTupleQuery();

final var nonRecurQuery = builder.createTupleQuery();
final var nonRecurRoot = nonRecurQuery.from(Account.class);
nonRecurQuery.multiselect(
    nonRecurRoot.get(Account_.id).alias("id"),
    nonRecurRoot.get(Account_.name).alias("name"),
    nonRecurRoot
        .get(Account_.parent)
        .get(Account_.id)
        .alias("parent_id"));
nonRecurQuery.where(
    builder.like(
        builder.upper(nonRecurRoot.get(Account_.name)), "%LIABILITY%"));
final var accountChainTable =
    query.withRecursiveUnionAll(
        nonRecurQuery,
        (cte) -> {
          final var innerQuery = builder.createTupleQuery();
          final var accountRoot = innerQuery.from(Account.class);
          final var cteRoot = innerQuery.from(cte);
          innerQuery.multiselect(
              accountRoot.get(Account_.id),
              accountRoot.get(Account_.name),
              accountRoot.get(Account_.parent).get(Account_.id));
          innerQuery.where(
              builder.equal(
                  accountRoot.get(Account_.id),
                  cteRoot.get("parent_id")));
          return innerQuery;
        });

final var root = query.from(accountChainTable);
query.multiselect(
    root.get("id"), root.get("name"), root.get("parent_id"));

for (final var tuple : session.createQuery(query).list()) {
  log.info("=== Account:");
  for (final var item : tuple.toArray()) {
    log.info("  - item: {}", item);
  }
}

The error is:

java.lang.IllegalArgumentException: Already registered a copy: org.hibernate.query.sqm.tree.cte.SqmCteStatement@19b19f46
	at org.hibernate.query.sqm.tree.SqmCopyContext$1.registerCopy(SqmCopyContext.java:40)
	at org.hibernate.query.sqm.tree.cte.SqmCteStatement.copy(SqmCteStatement.java:149)
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.copyCteStatements(AbstractSqmSelectQuery.java:73)
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.copy(SqmSelectStatement.java:125)
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.copy(SqmSelectStatement.java:42)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.<init>(QuerySqmImpl.java:228)
	at org.hibernate.internal.AbstractSharedSessionContract.createCriteriaQuery(AbstractSharedSessionContract.java:1343)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:1304)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:120)

Any idea?

2

Any updates? I think it’s a severe problem since it blocks the basic feature for Criteria API

3

@UkonnRa I’ve commented on your issue, it would be nice to have a reproducer for this.

4

Hi, the test case has been updated several days ago, please check.

Thanks for your great work supporting Hibernate project.