Caused by: org.postgresql.util.PSQLException: ERROR: column "generatedalias0" does not exist or "must appear in the GROUP BY" in query with no group

Question

I have 2 entities like that:

@Entity
@Table(name = "registry_groups")
public class RegistryGroupEntity {
    @Id
    @SequenceGenerator(name="registry_groups_gen", sequenceName="registry_groups_id_seq", allocationSize = 1)
    @GeneratedValue(generator="registry_groups_gen")
    private Integer id;
    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "registryGroup")
    private Collection<ServiceEntity> services;
//setters-getters ommited
}

@Entity
@Table(name = "services")
public class ServiceEntity {
    @Id
    @SequenceGenerator(name="service_gen", sequenceName="services_id_seq", allocationSize = 1)
    @GeneratedValue(generator="service_gen")
    private Integer id;
    @Column(nullable = false, length = 100)
    private String name;

    @Column(name = "registry_group_id", insertable = false, updatable = false)
    private Integer registryGroupId;

}

And I try to make a counter function based on the second entity joined to the firs one.

The first approach was:

public long getRegistryGroupSimpleServicesIntCount(String name) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Long> c = cb.createQuery(Long.class);
        Root<RegistryGroupEntity> registryGroup = c.from(RegistryGroupEntity.class);

        Join<RegistryGroupEntity, ServiceEntity> serviceJoin = registryGroup.join(RegistryGroupEntity_.services, JoinType.INNER);
        //

        //Subquery<String> concatSubQuery = c.subquery(String.class);
        //Root<RegistryGroupEntity> countSubQueryRoot = concatSubQuery.from(RegistryGroupEntity.class);
        //concatSubQuery.select(serviceJoin.get(ServiceEntity_.name));
        //https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function

        c.select(cb.count(serviceJoin.getSelection()));
        c.orderBy(cb.desc(serviceJoin.get(ServiceEntity_.name)));

        List<Predicate> criteria = new ArrayList<>();
        Map<String, Object> paramValues = new HashMap<>();
        if (name != null) {
            ParameterExpression<String> p = cb.parameter(String.class, "name");
            criteria.add(cb.like(serviceJoin.get(ServiceEntity_.name), p));
            paramValues.put("name", name);
        }
        if (criteria.size() == 1) {
            c.where(criteria.get(0));
        } else if (criteria.size() > 0) {
            c.where(cb.and(criteria.toArray(new Predicate[0])));
        }

        TypedQuery<Long> q = entityManager.createQuery(c);

        for (String paramName : paramValues.keySet()) {
            q.setParameter(paramName, paramValues.get(paramName));
        }

        return q.getSingleResult();
    }

Generate HQL is:

select count(services1_.id) as col_0_0_ from registry_groups registrygr0_ inner join services services1_ on registrygr0_.id=services1_.registry_group_id order by services1_.name desc

and the error message is:

ERROR: column "services1_.name" must appear in the GROUP BY clause or be used in an aggregate function

however, as you see, there is no group by clause in the query.

So, with enabling the subquery I have tried another approach:

public long getRegistryGroupSimpleServicesIntCount(String name) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery c = cb.createQuery(Long.class); Root registryGroup = c.from(RegistryGroupEntity.class);

    Join<RegistryGroupEntity, ServiceEntity> serviceJoin = registryGroup.join(RegistryGroupEntity_.services, JoinType.INNER);
    //здесь мы хотим получить все возможные услуги для поиска в фильре

    Subquery<String> concatSubQuery = c.subquery(String.class); //here the new part starts
    Root<RegistryGroupEntity> countSubQueryRoot = concatSubQuery.from(RegistryGroupEntity.class);
    concatSubQuery.select(serviceJoin.get(ServiceEntity_.name));
    //https://stackoverflow.com/questions/19601948/must-appear-in-the-group-by-clause-or-be-used-in-an-aggregate-function

    c.select(cb.count(countSubQueryRoot)); //here ends
    c.orderBy(cb.desc(serviceJoin.get(ServiceEntity_.name)));

    List<Predicate> criteria = new ArrayList<>();
    Map<String, Object> paramValues = new HashMap<>();
    if (name != null) {
        ParameterExpression<String> p = cb.parameter(String.class, "name");
        criteria.add(cb.like(serviceJoin.get(ServiceEntity_.name), p));
        paramValues.put("name", name);
    }
    if (criteria.size() == 1) {
        c.where(criteria.get(0));
    } else if (criteria.size() > 0) {
        c.where(cb.and(criteria.toArray(new Predicate[0])));
    }

    TypedQuery<Long> q = entityManager.createQuery(c);

    for (String paramName : paramValues.keySet()) {
        q.setParameter(paramName, paramValues.get(paramName));
    }

    return q.getSingleResult();
}

But now I'm getting another error with the followng reason in the stacktrace:

Caused by: org.postgresql.util.PSQLException: ERROR: column "generatedalias0" does not exist
  Position: 14

and the generated HQL is:

select count(generatedAlias0) from RegistryGroupEntity as generatedAlias1 inner join generatedAlias1.services as generatedAlias2 order by generatedAlias2.name desc

How to work it around with still trying to get the data from the join?