Select item at position 1 in select list has no alias (aliases are required in CTEs and in subqueries occurring in from clause)

Hibernate ORM
1 
org.hibernate.query.SemanticException: Select item at position 1 in select list has no alias (aliases are required in CTEs and in subqueries occurring in from clause)

String hql1 = """
                SELECT COUNT(d.id) FROM (SELECT
                    rule
                FROM
                    UserGroupDynamicRuleEntity rule
                    INNER JOIN UserGroupEntity group ON rule.userGroupId = group.id
                WHERE
                    group.type=:type) d
                """;
        TypedQuery<Integer> query1 = entityManager.createQuery(hql1,
                Integer.class);

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

1 Like
2

In Hibernate, every selected column in a subquery must have an alias. In the modified query, rule.id is given the alias rule_id, which is then used in the COUNT function as d.rule_id. This should resolve the SemanticException you’re facing. Remember to replace rule.id with the actual column you wish to count if it’s different.

String hql1 = “”"
SELECT COUNT(d.rule_id) FROM (SELECT
rule.id as rule_id
FROM
UserGroupDynamicRuleEntity rule
INNER JOIN UserGroupEntity group ON rule.userGroupId = group.id
WHERE
group.type=:type) d
“”";
TypedQuery query1 = entityManager.createQuery(hql1, Integer.class);