Possible regression 5.6 to 6.1 - [SqmRoot not yet resolved to TableGroup]

Jan_Vissers · August 3, 2022, 8:09am

I’m in the process of migrating an application to use 6.1 series of Hibernate from 5.6 and am noticing a nasty regression (?) issue. It is quite hard for me to isolate the case, so let me start by showing the code that worked fine prior to 6.1 and which has stopped working now.

@Dependent
public class CountFeature extends AbstractBaseFeature<CountResult, Long> {

  public <T extends BaseEntity<?>> Long exec(FeatureContext<T, CountResult> context) {

    if (!context.option.isTotalResults()) return null;

    var query = context.query;
    var builder = context.cb;

    CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
    countQuery.select(builder.count(context.root));
    countQuery.getRoots().addAll(query.getRoots());
    Optional.ofNullable(query.getRestriction()).ifPresent(countQuery::where);

    return context.em.createQuery(countQuery).getSingleResult();
  }
}

In 6.1 this now produces:

java.lang.IllegalArgumentException: org.hibernate.query.sqm.InterpretationException: Error interpreting query [SqmRoot not yet resolved to TableGroup]; this may indicate a semantic (user query) problem or a bug in the parser [SqmRoot not yet resolved to TableGroup]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:141)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:374)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.list(QuerySqmImpl.java:986)
	at org.hibernate.query.spi.AbstractSelectionQuery.getSingleResult(AbstractSelectionQuery.java:457)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.getSingleResult(QuerySqmImpl.java:1016)
	at CountFeature.exec(CountFeature.java:26)

Jan_Vissers · August 3, 2022, 8:20am

I have tried two things - to see what the behavior is:

Experiment 1

    CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
    Root<T> root = countQuery.from(context.resultClass);
    countQuery.select(builder.count(root));
//    Optional.ofNullable(query.getRestriction()).ifPresent(countQuery::where);

Technically this works, but the result is not okay, because I commented the criteria.

Experiment 2

    CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
    Root<T> root = countQuery.from(context.resultClass);
    countQuery.select(builder.count(root));
    Optional.ofNullable(query.getRestriction()).ifPresent(countQuery::where);

This gives me another exception:

java.lang.IllegalArgumentException: Already registered a copy: SqmSingularJoin(com.oracle.decs.designer.workbench.entities.TradingPartnerAgreementEntity(8835585279920).tradingPartner(8835587841336) : tradingPartner)
	at org.hibernate.query.sqm.tree.SqmCopyContext$1.registerCopy(SqmCopyContext.java:33)
	at org.hibernate.query.sqm.tree.domain.SqmSingularJoin.copy(SqmSingularJoin.java:63)
	at org.hibernate.query.sqm.tree.domain.SqmSingularJoin.copy(SqmSingularJoin.java:25)
	at org.hibernate.query.sqm.tree.domain.SqmBasicValuedSimplePath.copy(SqmBasicValuedSimplePath.java:58)
	at org.hibernate.query.sqm.tree.domain.SqmBasicValuedSimplePath.copy(SqmBasicValuedSimplePath.java:26)
	at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.copy(SqmComparisonPredicate.java:69)
	at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.copy(SqmComparisonPredicate.java:20)
	at org.hibernate.query.sqm.tree.predicate.SqmJunctionPredicate.copy(SqmJunctionPredicate.java:56)
	at org.hibernate.query.sqm.tree.predicate.SqmJunctionPredicate.copy(SqmJunctionPredicate.java:23)
	at org.hibernate.query.sqm.tree.predicate.SqmWhereClause.copy(SqmWhereClause.java:33)
	at org.hibernate.query.sqm.tree.select.SqmQuerySpec.copy(SqmQuerySpec.java:104)
	at org.hibernate.query.sqm.tree.select.SqmQuerySpec.copy(SqmQuerySpec.java:53)
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.copy(SqmSelectStatement.java:122)
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.copy(SqmSelectStatement.java:42)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.<init>(QuerySqmImpl.java:217)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:1299)
	at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
	at io.helidon.integrations.cdi.jpa.DelegatingEntityManager.createQuery(DelegatingEntityManager.java:298)
	at io.helidon.integrations.cdi.jpa.CdiTransactionScopedEntityManager$Proxy$_$$_WeldClientProxy.createQuery(Unknown Source)
	at io.helidon.integrations.cdi.jpa.DelegatingEntityManager.createQuery(DelegatingEntityManager.java:298)
	at io.helidon.integrations.cdi.jpa.JpaTransactionScopedEntityManager.createQuery(JpaTransactionScopedEntityManager.java:576)
	at CountFeature.exec(CountFeature.java:31)

Jan_Vissers · August 3, 2022, 8:38am

Apparently in Hibernate 6.1 the CriteriaCompiler that used to be there in H5.x has been replaced by something else, judging from the comment on that class:

/**
 * Compiles a JPA criteria query into an executable {@link TypedQuery}.  Its single contract is the {@link #compile}
 * method.
 * <p/>
 * NOTE : This is a temporary implementation which simply translates the criteria query into a JPAQL query string.  A
 * better, long-term solution is being implemented as part of refactoring the JPAQL/HQL translator.
 *
 * @author Steve Ebersole
 */

Unfortunately that better, long-term solution isn’t working for me now - whilst the criteria query didn’t change. Should I drive this differently from JPA/JPQL now?

Jan_Vissers · August 3, 2022, 10:42am

My suspicion is that I cannot reuse information (e.g. predicates) from other query objects (anymore).

In Experiment 3 - I have replaced the restriction part, by writing it out by hand:

So instead of: Optional.ofNullable(query.getRestriction()).ifPresent(countQuery::where);

I hacked:

    Join<Object, Object> tradingPartner = root.join("tradingPartner");
    countQuery.where(builder.equal(tradingPartner.get("id"),1L));

Which is reflecting this specific test case, and in that case - the count query succeeds.

Bottom-line: seems like with SQM I cannot reuse information from other query objects, is that correct and what are my options? I mean, the countQuery feature is a generic piece in our code base…

beikov · August 3, 2022, 10:45am

What you are doing here is illegal according to the JPA spec. Also see the java doc of CriteriaQuery#getRoots:

    /**
     * Return the query roots.  These are the roots that have
     * been defined for the <code>CriteriaQuery</code> or <code>Subquery</code> itself,
     * including any subquery roots defined as a result of
     * correlation. Returns empty set if no roots have been defined.
     * Modifications to the set do not affect the query.
     * @return the set of query roots
     */   
    Set<Root<?>> getRoots();

The error essentially is, that something in your query (select item) refers to nodes (SqmRoot) which are not part of the `CriteriaQuery.

The operation countQuery.getRoots().addAll(query.getRoots()); doesn’t do anything to the CriteriaQuery as per the java doc.

If you want, you can create a JIRA issue to improve the error message, but the code you wrote won’t work anymore in 6.0. If it worked before, then this was in violation to the JPA specification.

With 6.1 you can make use of the derived subquery support though to implement this. You can use code similar to the following:

CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
JpaSubQuery<Tuple> subquery = countQuery.subquery(Tuple.class);
// Apply everything from context.query also to subquery
JpaRoot<Tuple> root = countQuery.from(subquery);
countQuery.select(builder.count(root));

If you can’t apply everything from context.query to subquery for whatever reason, then you’ll have to find a different solution, but note that you can make use of the copy functionality of the SQM query model to easily do this. This would look something like this:

SqmSubQuery<Tuple> subquery = (SqmSubQuery<Tuple>) countQuery.subquery(Tuple.class);
SqmSelectStatement originalQuery = (SqmSelectStatement) context.query;
SqmQuerySpec querySpec = originalQuery.getQuerySpec();
subquery.setQueryPart(querySpec.copy(SqmCopyContext.simpleContext()));

Jan_Vissers · August 3, 2022, 10:59am

Thank so much - for taking the time and look at my issue.

I’m trying to apply your first suggestion, but I’m afraid that I am missing a couple of things.
So a couple of things:

where you are saying Tuple do you mean jakarta.persistence.Tuple?
is that correct though, because if I do that - the countQuery.from(subQuery) does not compile

Jan_Vissers · August 3, 2022, 11:16am

I now have this:

public <T extends BaseEntity<?>> Long exec(FeatureContext<T, CountResult> context) {

    if (!context.option.isTotalResults()) return null;

    var query = context.query;
    var builder = context.cb;

    CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
    var subQuery = countQuery.subquery(Tuple.class);
    Optional.ofNullable(query.getRestriction()).ifPresent(subQuery::where);
    var root = countQuery.from(subQuery.getJavaType());
    countQuery.select(builder.count(root));

    return context.em.createQuery(countQuery).getSingleResult();
  }

Which results in:

Caused by: java.lang.IllegalArgumentException: Not an entity: jakarta.persistence.Tuple
	at org.hibernate.metamodel.model.domain.internal.JpaMetamodelImpl.entity(JpaMetamodelImpl.java:192)
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.from(AbstractSqmSelectQuery.java:135)
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.from(AbstractSqmSelectQuery.java:36)

What is obvious is that I’m not sure what to put into the countQuery.from

beikov · August 3, 2022, 11:19am

You need something like this:

public <T extends BaseEntity<?>> Long exec(FeatureContext<T, CountResult> context) {

    if (!context.option.isTotalResults()) return null;

    var query = context.query;
    var builder = (HibernateCriteriaBuilder) context.cb;

    var countQuery = builder.createQuery(Long.class);
    var subQuery = countQuery.subquery(Tuple.class);
    Optional.ofNullable(query.getRestriction()).ifPresent(subQuery::where);
    var root = countQuery.from(subQuery);
    countQuery.select(builder.count(root));

    return context.em.createQuery(countQuery).getSingleResult();
  }

The Optional.ofNullable(query.getRestriction()).ifPresent(subQuery::where); part can’t possibly work though.

Jan_Vissers · August 3, 2022, 11:21am

I’ve added that cast and remove the line Optional.ofNullable - still gives me

java.lang.IllegalArgumentException: Not an entity: jakarta.persistence.Tuple

though.

beikov · August 3, 2022, 11:39am

Look closely. I wrote var root = countQuery.from(subQuery);

Jan_Vissers · August 3, 2022, 1:18pm

Yup - thanks I missed that earlier. I’m still facing some issues - but let me investigate that a bit further myself.

Jan_Vissers · August 3, 2022, 1:53pm

Still struggling - sorry.

The query now reports Caused by: java.lang.IllegalArgumentException: subquery has no selection items.

I’m pretty sure this comment you made:

// Apply everything from context.query also to subquery

is related to that. Effectively I just want to repeat what was there in the original query. The use case is that the original query returns the actual results (possibly limited by a page size) and that the count returns the overall count for that query (unlimited).

I feel that this is the last push, but I’m struggling to get this done.

Jan_Vissers · August 3, 2022, 2:21pm

@Dependent
@SuppressWarnings({"rawtypes", "unchecked"})
public class CountFeature extends AbstractBaseFeature<CountResult, Long> {

  public <T extends BaseEntity<?>> Long exec(FeatureContext<T, CountResult> context) {

    if (!context.option.isTotalResults()) return null;

    var query = context.query;
    var builder = (HibernateCriteriaBuilder) context.cb;

    var countQuery = builder.createQuery(Long.class);
    var subQuery = countQuery.subquery(Tuple.class);

    var sqmSubQuery = (SqmSubQuery<Tuple>) subQuery;
    var sqmOriginalQuery = (SqmSelectStatement) query;
    var sqmOriginalQuerySpec = sqmOriginalQuery.getQuerySpec();
    sqmSubQuery.setQueryPart(sqmOriginalQuerySpec.copy(SqmCopyContext.simpleContext()));

    var root = countQuery.from(subQuery);
    countQuery.select(builder.count(root));

    var count =  context.em.createQuery(countQuery).getSingleResult();

    return count;

  }
}

Results in:

java.lang.IllegalArgumentException: Component at index 0 has no alias, but alias is required
	at org.hibernate.query.derived.AnonymousTupleType.<init>(AnonymousTupleType.java:58)
	at org.hibernate.query.derived.AnonymousTupleType.<init>(AnonymousTupleType.java:47)
	at org.hibernate.query.sqm.tree.domain.SqmDerivedRoot.<init>(SqmDerivedRoot.java:38)
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.from(AbstractSqmSelectQuery.java:157)
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.from(AbstractSqmSelectQuery.java:146)
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.from(AbstractSqmSelectQuery.java:36)
	at CountFeature.exec(CountFeature.java:39)

The use case seems simple enough. In Plain SQL for me this would be:

Query 1:

select t.* from table t where t.a = ? …

Query 2:

select count()
from ( select t. from table t where t.a = ? … )

So after this statement:

sqmSubQuery.setQueryPart(sqmOriginalQuerySpec.copy(SqmCopyContext.simpleContext()));

The HSQL for the subquery, looks okay to me:

(select alias_160848076 from TradingPartnerAgreementEntity alias_160848076 join alias_160848076.tradingPartner alias_1555604873 where alias_1555604873.id = 1)

But then the next statement: countQuery.from(subQuery) is failing with that alias exception. I’m confused.

edit

So apparently I have to create the following kind of query:

 var query = context.em.createQuery("""
        select count(d.id) from ( select e.id as id from TradingPartnerAgreementEntity e where e....) as d
        """, Tuple.class);
    var r = query.getSingleResult();

Now the only thing to do, is figure out how I can mangle the original pieces into the correct shape. (only :D) - which is not really that easy.

Jan_Vissers · August 3, 2022, 2:57pm

(post deleted by author)

Jan_Vissers · August 4, 2022, 8:11am

I’m stuck, as I cannot imagine how to construct the desired query, using the criteria API. Seems like subquery is getting in the way. Could really use a little more help.

So the original query is something like this:

select e
from   entity e
where  <restrictions>

And what I need is something like:

select count(d.id)
from   (  select  e.id  as id     -- id column is always going to be there
          from    entity e
          where   <restrictions>  -- same/copy as before
       ) as d

beikov · August 4, 2022, 8:25am

Like I wrote before, you can use the copy method of the SQM API:

beikov:

SqmSubQuery<Tuple> subquery = (SqmSubQuery<Tuple>) countQuery.subquery(Tuple.class);
SqmSelectStatement originalQuery = (SqmSelectStatement) context.query;
SqmQuerySpec querySpec = originalQuery.getQuerySpec();
subquery.setQueryPart(querySpec.copy(SqmCopyContext.simpleContext()));

After doing that, you can replace selection items in the subquery in case you don’t need them, or simply provide some generic aliases.

Jan_Vissers · August 4, 2022, 8:42am

I think I’ve gotten a little further. With this - I get passed the alias problem:

  public <T extends BaseEntity<?>> Long exec(FeatureContext<T, CountResult> context) {

    if (!context.option.isTotalResults()) return null;

    var query = context.query;
    var builder = (HibernateCriteriaBuilder) context.cb;

    var countQuery = builder.createQuery(Long.class);
    var subQuery = countQuery.subquery(Tuple.class);

    var sqmSubQuery = (SqmSubQuery<Tuple>) subQuery;
    var sqmOriginalQuery = (SqmSelectStatement) query;
    var sqmOriginalQuerySpec = sqmOriginalQuery.getQuerySpec();
    var sqmSubQuerySpec = sqmOriginalQuerySpec.copy(SqmCopyContext.simpleContext());

    sqmSubQuery.setQueryPart(sqmSubQuerySpec);
    Root<T> subQuerySelectRoot = subQuery.from(context.resultClass);
    sqmSubQuery.multiselect(subQuerySelectRoot.get("id").alias("id"));

    var root = countQuery.from(sqmSubQuery);
    countQuery.select(builder.count(root));

    var count = context.em.createQuery(countQuery).getSingleResult();

    return count;
  }

However - the next error shows up here:

assert actualModelPart instanceof BasicValuedModelPart;

While the query is executed:

Caused by: java.lang.AssertionError
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitTableGroup(BaseSqmToSqlAstConverter.java:3651)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitRootDerived(BaseSqmToSqlAstConverter.java:3417)
	at org.hibernate.query.sqm.tree.domain.SqmDerivedRoot.accept(SqmDerivedRoot.java:95)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWithInferredType(BaseSqmToSqlAstConverter.java:6175)
	at org.hibernate.query.sqm.function.SelfRenderingSqmFunction.resolveSqlAstArguments(SelfRenderingSqmFunction.java:133)
	at org.hibernate.query.sqm.function.SelfRenderingSqmAggregateFunction.convertToSqlAst(SelfRenderingSqmAggregateFunction.java:83)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFunction(BaseSqmToSqlAstConverter.java:5390)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFunction(BaseSqmToSqlAstConverter.java:414)
	at org.hibernate.query.sqm.tree.expression.SqmFunction.accept(SqmFunction.java:67)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelection(BaseSqmToSqlAstConverter.java:2015)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectClause(BaseSqmToSqlAstConverter.java:1958)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:1826)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:414)
	at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:122)
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:211)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1686)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:1485)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:414)
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.accept(SqmSelectStatement.java:199)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(BaseSqmToSqlAstConverter.java:707)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.buildCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:350)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:270)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:246)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:537)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:363)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.list(QuerySqmImpl.java:987)
	at org.hibernate.query.spi.AbstractSelectionQuery.getSingleResult(AbstractSelectionQuery.java:457)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.getSingleResult(QuerySqmImpl.java:1017)

At that time actualModelPart for me is an AnonymousTypleTableGroupProducer

beikov · August 4, 2022, 8:46am

Can you please create a JIRA issue for this? Either way, I would recommend you use this instead:

countQuery.select(builder.count(builder.literal(1)));

Jan_Vissers · August 4, 2022, 8:57am

I will create a Jira issue for this yes (do I need some entitlements to do that?).

With your last addition the query works, but the statement is wrong:

Hibernate: 
    select
        count(1) 
    from
        (select
            t3_0.TRADING_PARTNER_AGREEMENT_ID 
        from
            TRA_TRADING_PARTNER_AGREEMENT t1_0,
            TRA_TRADING_PARTNER_AGREEMENT t3_0 
        where
            t1_0.TRADING_PARTNER_ID=?) derived1_0(id)
2022.08.04 10:53:14 TRACE org.hibernate.orm.jdbc.bind Thread[helidon-server-2,5,server]: binding parameter [1] as [BIGINT] - [1]

This is the current code:

  public <T extends BaseEntity<?>> Long exec(FeatureContext<T, CountResult> context) {

    if (!context.option.isTotalResults()) return null;

    var query = context.query;
    var builder = (HibernateCriteriaBuilder) context.cb;

    var countQuery = builder.createQuery(Long.class);
    var subQuery = countQuery.subquery(Tuple.class);

    var sqmSubQuery = (SqmSubQuery<Tuple>) subQuery;
    var sqmOriginalQuery = (SqmSelectStatement) query;
    var sqmOriginalQuerySpec = sqmOriginalQuery.getQuerySpec();
    var sqmSubQuerySpec = sqmOriginalQuerySpec.copy(SqmCopyContext.simpleContext());

    sqmSubQuery.setQueryPart(sqmSubQuerySpec);
    Root<T> subQuerySelectRoot = subQuery.from(context.resultClass);
    sqmSubQuery.multiselect(subQuerySelectRoot.get("id").alias("id"));

    var root = countQuery.from(sqmSubQuery);
    countQuery.select(builder.count(builder.literal(1)));

    var count = context.em.createQuery(countQuery).getSingleResult();

    return count;
  }

beikov · August 4, 2022, 9:06am

I will create a Jira issue for this yes (do I need some entitlements to do that?).

No, you just go to https://hibernate.atlassian.net/ and after registration you create an issue.

With your last addition the query works, but the statement is wrong:

What do you mean by “wrong”? Looks ok to me. Note that count(1) is equivalent to count(*) semantically.

Topic		Replies	Views
SqmRoot not yet resolved to TableGroup Hibernate ORM	1	1536	October 19, 2023
Polymorphic queries in hibernate 6 Hibernate ORM	2	910	June 1, 2023
SqmAttributeJoin not yet resolved to TableGroup Hibernate ORM	5	40	September 25, 2024
Regression in 6.2.3 with Spring Data JPA derived query Hibernate ORM	3	490	September 6, 2023
createCountQuery() failing in Hibernate 6.4.2.Final Hibernate ORM	7	1142	July 9, 2024

Possible regression 5.6 to 6.1 - [SqmRoot not yet resolved to TableGroup]

Related topics