createCountQuery with Hibernate 6.6.2

Hibernate ORM
1

When creating a countQuery the countQuery contains the orderby criteria from the source query, and if this is also a distinct query, the countQuery fails:

final CriteriaQuery<Organization> query = criteriaQuery
                .where(criteriaBuilder.and(predicates.toArray(Predicate[]::new)));
        query.distinct(true);

        final Sort sortBy = page.getSortOr(Sort.by(Sort.Direction.ASC, Fields.someId));

        query.orderBy(sortBy.stream().map(order -> order.isAscending() //
                        ? criteriaBuilder.asc(root.get(order.getProperty())) //
                        : criteriaBuilder.desc(root.get(order.getProperty()))).toList());

         **((SqmSelectStatement<Organization>) query).createCountQuery();**

This ends in sql like this:

    select
        count(*) 
    from
        (select
            distinct o1_0.id 
        from
            orgs o1_0 
        where
            o1_0.groups && cast(array['COMMON,'SPECIAL'] as varchar array)::text[] 
            and lower(o1_0.id)=? 
            order by
            o1_0.org_id) derived1_0(c0_id)

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

How can I avoid this?

2

What version of Hibernate are you using? This might be a bug, please try to create a reproducer using our test case template and if you are able to reproduce the issue, create a new ticket in our issue tracker and attach that reproducer.

3

Hi, thanks for the quick reply!
I used 6.6.2.
Issue created:
https://hibernate.atlassian.net/browse/HHH-18850

Reproducer is attached.