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);
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);
I had the same exception and couldn’t figure out the problem from the exception message, so I had to debug the code and only then figured it out.
Would it be possible/sensible to infer an alias from the component itself? Like some subtypes of `SqmSelectableNode` seem to have a `NavigablePath.localName` - for example, if a components `NavigablePath.localName` would be `numeredContact`, could one reuse it as an alias? Or at least put this information in the exception message?
