Recursive CTE possible in Subselects or Joins?

Hibernate ORM
1

Hi,

is it possbile to use recursive CTEs in Subselects or Joins? For example:

final TypedQuery<String> testQuery = entityManager.createQuery("""
				SELECT c.name FROM Container c 
				WHERE c.location.id IN 
				(WITH allLocations AS (SELECT l.name name, l.id id FROM Location l WHERE l.id = :startId
				UNION ALL
				SELECT l2.name name, l2.id id FROM allLocations al JOIN Location l2 ON al.id = l2.parent.id )
				SELECT al.id FROM allLocations al)""", String.class);

testQuery.setParameter("startId", location.getId());

final List<String> containerNames = testQuery.getResultList();

The pure CTE delivers the expected result. But in Subselect I get the following error:

Caused by: org.hibernate.query.sqm.InterpretationException: Error interpreting query [SELECT c.name FROM Container c
WHERE c.location.id IN
(WITH allLocations AS (SELECT l.name name, l.id id FROM Location l WHERE l.id = :startId
UNION ALL
SELECT l2.name name, l2.id id FROM allLocations al JOIN Location l2 ON al.id = l2.parent.id )
SELECT al.id FROM allLocations al)]; this may indicate a semantic (user query) problem or a bug in the parser [SELECT c.name FROM Container c
WHERE c.location.id IN
(WITH allLocations AS (SELECT l.name name, l.id id FROM Location l WHERE l.id = :startId
UNION ALL
SELECT l2.name name, l2.id id FROM allLocations al JOIN Location l2 ON al.id = l2.parent.id )
SELECT al.id FROM allLocations al)]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:97) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:165) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:147) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:741) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:786) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	... 128 more
Caused by: java.lang.ClassCastException: class org.hibernate.query.sqm.tree.select.SqmSelectStatement cannot be cast to class org.hibernate.query.sqm.tree.select.SqmSubQuery (org.hibernate.query.sqm.tree.select.SqmSelectStatement and org.hibernate.query.sqm.tree.select.SqmSubQuery are in unnamed module of loader org.apache.catalina.loader.ParallelWebappClassLoader @32a25711)
	at org.hibernate.query.sqm.tree.cte.SqmCteStatement.<init>(SqmCteStatement.java:92) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.withInternal(AbstractSqmSelectQuery.java:177) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.withRecursiveUnionAll(AbstractSqmSelectQuery.java:127) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitCte(SemanticQueryBuilder.java:761) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWithClause(SemanticQueryBuilder.java:663) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$WithClauseContext.accept(HqlParser.java:1122) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:928) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1711) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSubquery(SemanticQueryBuilder.java:5075) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInPredicate(SemanticQueryBuilder.java:2695) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInPredicate(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$InPredicateContext.accept(HqlParser.java:6086) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:2297) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$WhereClauseContext.accept(HqlParser.java:5910) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuery(SemanticQueryBuilder.java:1161) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:937) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$QuerySpecExpressionContext.accept(HqlParser.java:1818) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:931) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1711) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement(SemanticQueryBuilder.java:418) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement(SemanticQueryBuilder.java:377) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel(SemanticQueryBuilder.java:295) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:81) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:165) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:147) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:741) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:786) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	... 128 more
2

Hey, that looks like a bug to me.
Please try to create a reproducer with our test case template (hibernate-test-case-templates/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) and if you are able to reproduce the issue, create a bug ticket in our issue tracker(https://hibernate.atlassian.net) and attach that reproducer.

3

I have tested with plain SQL in SQL SERVER and it is also not possible to use CTE in SUBSELECT. Then it is not an bug in hibernate.

4

Please create the reproducer and the bug report. Hibernate ORM should actually hoist the CTE into the top level in this case.