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

Raised [HHH-15434] - Hibernate JIRA with a detailed explanation. Hope you can have another look (and I really appreciate your time!)

I meant a JIRA issue for the AssertionError that you saw :slight_smile:

I’m sorry - about that. Let me create that (too) then.

https://hibernate.atlassian.net/browse/HHH-15435

Thanks a lot. By the way, you can also join us on Zulip for a better chat experience :slight_smile:

1 Like

Hi,

I have the same problem as Jan!

using

 sqmSubQuery.multiselect(subQuerySelectRoot.get("id").alias("id"));

results in
IllegalStateException: The JPA specification does not support subqueries having multiple select items. Please disable the JPA query compliance if you want to use this feature.

I am using WildFly-27.0.1.Final

Well, it’s simply illegal to use multi-select with subqueries in JPA, so switch to using select() instead.

but replacing the multiselect

//        sqmSubQuery.multiselect(subQuerySelectRoot.get("id").alias("id"));
        sqmSubQuery.select(subQuerySelectRoot.get("id"));

results in

IllegalStateException: The JPA specification does not support subqueries in the from clause. Please disable the JPA query compliance if you want to use this feature.

me approach is the follwing;

// 1. Get total numbers of entities
long totalCount = `select count(*) from entities where <some predicates>`
if (count > 0) {
    // 2. Get paged entities
    List<Entity> list = `select * from entities where <some predicates> limit 20 offset 100`
}
return new PagedList(totalCount, list);

I don’t know what this pseudo-code should represent, but the error is very much understandable and actionable.
If you think the error is incorrect, please share your full query or create a test case(https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) that reproduces the issue so we can look into this.

I solved it this way for Hibernate 6:

    public PagedList<AufgabeDTO> getAufgabeDTOs(final LoadOptions loadOptions) {
        PagedList<AufgabeDTO> result = PagedList.empty();

        final var funktionFilter = loadOptions.detachFilterOption(Constants.FUNKTION_FILTER_OPTION).orElse(null);
        final var benutzerFilter = loadOptions.detachFilterOption(Constants.BENUTZER_FILTER_OPTION).orElse(null);
        final var fixedLoadOptions = fixLoadOptions(loadOptions);

        CriteriaBuilder countBuilder = repository.getEntityManager().getCriteriaBuilder();
        CriteriaQuery<Long> countQuery = countBuilder.createQuery(Long.class);
        Root<AufgabeDTO> countRoot = countQuery.from(AufgabeDTO.class);
        countQuery.select(countBuilder.countDistinct(countRoot));
        countQuery.where(getPredicates(countBuilder, countRoot, loadOptions, funktionFilter, benutzerFilter));
        final var tqCount = repository.getEntityManager().createQuery(countQuery.distinct(true));
        final var count = tqCount.getSingleResult();
        if (count > 0) {
            CriteriaBuilder cb = repository.getEntityManager().getCriteriaBuilder();
            CriteriaQuery<AufgabeDTO> cq = cb.createQuery(AufgabeDTO.class);
            Root<AufgabeDTO> aufgabeRoot = cq.from(AufgabeDTO.class);
            cq.select(aufgabeRoot);
            cq.where(getPredicates(cb, aufgabeRoot, loadOptions, funktionFilter, benutzerFilter));
            final var tq = repository.getEntityManager().createQuery(cq.distinct(true));
            final var data = tq.getResultList();
            result = new PagedList<>(count, data);
        }
        return result;
    }

Problem with Hibernate 6 is - as already mentioned - that I cannot share Predicate for multiple queries (in my case, query for count and query for selecting data).

So it seems I have to refactor my code - which will be probably a huge amount of work!

Is there any chance to copy the predicates from one query to another - but without using a sub-query?
Similar to the example above like:

var sqmSubQuerySpec = sqmOriginalQuerySpec.copy(SqmCopyContext.simpleContext());

Sharing of predicates never really worked, it was just a coincidence that you didn’t run into problems so far, so it’s definitely good that you rework your query building.

You can copy predicates, but you will have to create a mapping from the From nodes (Root and Join) of one query structure to the other query structure.

Using the subquery count approach that is depicted at the start of this thread is definitely doable though, you just have to disable JPA compliance because this is using features of Hibernate that are not part of the JPA specification. The error just tells you that you are relying on Hibernate specific features, but it seems you’re in the realm of using Hibernate specifics anyway, so you might as well just disable the compliance setting hibernate.jpa.compliance: Hibernate ORM User Guide

Thank you for your support!
To keep my code JPA compliant, I’ll try to copy the predicates. Do you have any hint or example where I can see how to do this?
If this fails, I’ll try to do it the non-JPA compliant way :wink:

I added this to my persistence.xml:

<!-- for Hibernate 6 -->
<property name="hibernate.jpa.compliance" value="false"/>

But still get this exception:

IllegalStateException: The JPA specification does not support subqueries having multiple select items. Please disable the JPA query compliance if you want to use this feature.
	at org.hibernate@6.1.5.Final//org.hibernate.query.sqm.tree.select.SqmSubQuery.validateComplianceMultiselect(SqmSubQuery.java:408)

Do I need to set this proberty in hibernate.cfg.xml? Currently I have only persistence.xml

Used now

<!-- for Hibernate 6 -->
<property name="hibernate.jpa.compliance.query" value="false"/>

in my persistence.xml.

hibernate.jpa.compliance is available in hiberante 6.3 - here the documentation is wrong, because it tells that this setting is available since 6.0!

Indeed. Maybe you can provide a PR that fixes the javadocs :wink:

Which project should I choose for the PR? There are lots of projects!